I have a table like this:
item1 | item2 | item3 |
A | B | A |
B | A | C |
A | C | |
A | B | A |
B | A | B |
B | A | |
C | A | B |
B | B | A |
and I would like to count the occruance of each level for each item and turn it into
A | B | C | (missing value) | |
item1 | 3 | 3 | 1 | 1 |
item2 | 3 | 4 | 1 | 1 |
item3 | 4 | 2 | 1 | 1 |
How can I do that? TIA!
One way.. @Yiting does the below code work for you?
data have;
input (item1 item2 item3)(:$);
infile datalines dlm=',';
datalines;
A,B,A
B,A,C
A,C,
A,B,A
B,A,B
,B,A
C,A,B
B,B,A
;
data temp(keep=var value);
set have;
array a item:;
do i=1 to dim(a);
value=a[i];
if value=' ' then value='Missing';
var=vname(a[i]);
output;
end;
run;
proc sql;
create table temp2 as
select *, count(value) as count
from temp
group by var, value
order by var;
quit;
proc transpose data=temp2 out=want(drop=_NAME_);
by var;
id value;
var count;
run;
One way.. @Yiting does the below code work for you?
data have;
input (item1 item2 item3)(:$);
infile datalines dlm=',';
datalines;
A,B,A
B,A,C
A,C,
A,B,A
B,A,B
,B,A
C,A,B
B,B,A
;
data temp(keep=var value);
set have;
array a item:;
do i=1 to dim(a);
value=a[i];
if value=' ' then value='Missing';
var=vname(a[i]);
output;
end;
run;
proc sql;
create table temp2 as
select *, count(value) as count
from temp
group by var, value
order by var;
quit;
proc transpose data=temp2 out=want(drop=_NAME_);
by var;
id value;
var count;
run;
Thanks! Solved the problem!
Anytime, glad to help 🙂
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
Check out this tutorial series to learn how to build your own steps in SAS Studio.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.