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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.