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 🙂
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.