If I have the following data
DATA raw;
INPUT group $ var1 $ var2 $;
CARDS;
A Y Y
A Y N
A N N
A Y Y
A Y Y
B N N
B N N
B N N
B Y N
;
run;
And I want my result to look like this:
Group value var1 var2 A Y 4 3 A N 1 2 B Y 1 0 B N 3 4
Assume that there are many more variable columns in the actual data set. How do I do this without just using proc tabulate on each variable and then manually joining the result tables together?
see if this helps
DATA raw;
INPUT group $ var1 $ var2 $;
CARDS;
A Y Y
A Y N
A N N
A Y Y
A Y Y
B N N
B N N
B N N
B Y N
;
run;
data t;
set raw;
array t(*) var:;
do i= 1 to dim(t);
vn=vname(t(i));
val=t(i);
output;
end;
keep group vn val;
run;
proc sql;
create table w2 as
select group, vn,val,count(val) as c
from t
group by group, vn,val
order by group ,val;
quit;
proc transpose data=w2 out=want(drop=_name_);
by group val;
var c;
id vn ;
run;
i
many columns is "how many"? (can you give us a rough estimate?)
about 20 or so. Essentially I want to avoid manually doing this.
I totally agree with you. Just thinking of how best that can be done
see if this helps
DATA raw;
INPUT group $ var1 $ var2 $;
CARDS;
A Y Y
A Y N
A N N
A Y Y
A Y Y
B N N
B N N
B N N
B Y N
;
run;
data t;
set raw;
array t(*) var:;
do i= 1 to dim(t);
vn=vname(t(i));
val=t(i);
output;
end;
keep group vn val;
run;
proc sql;
create table w2 as
select group, vn,val,count(val) as c
from t
group by group, vn,val
order by group ,val;
quit;
proc transpose data=w2 out=want(drop=_name_);
by group val;
var c;
id vn ;
run;
i
Yeah, this works. Thanks. So basically I have to change the original data set from wide to long. I wonder if proc transpose could be used to achieve that.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.