SAS Enterprise Guide – Split/Stack Columns

Question

I'm currently using SAS Enterprise Guide for one of my assignments and I'm trying to alter my current table to a desired table such as this one. I've already used the split/stack column task, but I'm not sure which variable(s) I should put in certain task role(s) – any suggestions?

Thank you!

Answer 1

Try something like this:

create temporary table data
(
    Gender varchar(1),
    Status varchar(20),
    Avg_Claim_Amt numeric(8,2)
);

insert into data values('F','Married_Yes',1546.19);
insert into data values('F','Married_No' ,2269.10);
insert into data values('M','Married_Yes',1485.45);
insert into data values('M','Married_No' ,2308.96);

select * from data;

select gender,sum(married_no) as Married_No,sum(married_yes) as Married_Yes
from
(
select gender,
       case when Status='Married_No'  then Avg_Claim_amt else 0.00 end as Married_No,
       case when Status='Married_Yes' then Avg_Claim_amt else 0.00 end as Married_Yes
from data) as x
group by gender;

 gender | married_no | married_yes
--------+------------+-------------
 M      |    2308.96 |     1485.45
 F      |    2269.10 |     1546.19

Answer 2

You want to transpose your dataset.

Try this out

proc transpose data = current_data out = new_data (drop=_name_);
by GENDER NOTSORTED;
id STATUS;
var Avg_Claim_amt;
run;

Best,

Answer 3

You want to use the TRANSPOSE task in SAS EG with the following parameters defined:

https://documentation.sas.com/?activeCdc=egdoccdc&cdcId=egcdc&cdcVersion=8.3&docsetId=egamotasks&docsetTarget=n0f6xtevrykn11n1n8n41ocko1cj.htm&locale=en&docsetVersion=8.3

If you're doing it via code use @LuizZ response instead. The task should build very similar code. Another option is to 'back up' and create that table via a different process or task that will generate it in the format desired at the start.