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 🙂
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.