topic Re: pivot table: transforming some columns into rows in SAS Programming

pivot table: transforming some columns into rows

catkat96 — Thu, 20 May 2021 15:04:44 GMT

I have a dataset in which each row is a date and there are many columns. All these columns are binary.

I wanted to know how to create a (pivot?) table where some of those columns were the rows now and the cells were the sum of those cases when varA is 1 and varB is one.

Let's say this is my data:

data Have;
 input id var1 var2 var3 varA varB ;
 datalines;
1 1 0 1 1 0 
2 0 0 1 0 1
3 0 1 0 0 0
4 1 0 0 1 0
5 1 1 1 1 1 
;
run;

This is the final result I'm looking for:

data want;
 input varA varB ;
 datalines;
3 1
1 1
2 2
;
run;

In this case the rows would be var1 var2 and va3 respectively.

How could I do this? Is it possible with a summary table? Could I do this for both when var A and varB are 1 and when they are 0, in the same table?

Re: pivot table: transforming some columns into rows

PaigeMiller — Thu, 20 May 2021 15:54:25 GMT

Its confusing to me how you take the HAVE data set and turn it into the WANT data set. Can you explain the logic?

Re: pivot table: transforming some columns into rows

Kurt_Bremser — Thu, 20 May 2021 16:05:18 GMT

See this:

data Have;
 input id var1 var2 var3 varA varB ;
 datalines;
1 1 0 1 1 0 
2 0 0 1 0 1
3 0 1 0 0 0
4 1 0 0 1 0
5 1 1 1 1 1 
;

data pretrans;
set have;
name = "varA";
v1 = var1 * vara;
v2 = var2 * vara;
v3 = var3 * vara;
output;
name = "varB";
v1 = var1 * varb;
v2 = var2 * varb;
v3 = var3 * varb;
output;
keep name v1-v3;
run;

proc summary data=pretrans nway;
class name;
var v1-v3;
output out=sum sum()=;
run;

proc transpose
  data=sum
  out=want
;
id name;
var v1-v3;
run;

if you need to make this more flexible, use arrays in the pretrans step.

Re: pivot table: transforming some columns into rows

Ksharp — Fri, 21 May 2021 13:28:41 GMT

Under the help of Kurt , I understand what you want now.

data Have;
 input id var1 var2 var3 varA varB ;
 datalines;
1 1 0 1 1 0 
2 0 0 1 0 1
3 0 1 0 0 0
4 1 0 0 1 0
5 1 1 1 1 1 
;
run;
proc sql;
create table want as
select sum(var1*varA) as varA,sum(var1*varB) as varB from have
union all
select sum(var2*varA) as varA,sum(var2*varB) as varB from have
union all
select sum(var3*varA) as varA,sum(var3*varB) as varB from have
;
quit;