🔒 This topic is solved and locked.
Need further help from the community? Please
sign in and ask a new question.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Posted 04-05-2019 07:42 AM
(1594 views)
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!
1 ACCEPTED SOLUTION
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
3 REPLIES 3
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thanks! Solved the problem!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Anytime, glad to help 🙂