SAS Enterprise Guide

Desktop productivity for business analysts and programmers
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Yiting
Quartz | Level 8

I have a table like this:

 

item1item2item3
ABA
BAC
AC 
ABA
BAB
 BA
CAB
BBA

 

and I would like to count the occruance of each level for each item and turn it into 

 

 ABC(missing value)
item13311
item23411
item34211

 

How can I do that? TIA!

1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20

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;

 

View solution in original post

3 REPLIES 3
PeterClemmensen
Tourmaline | Level 20

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;

 

Yiting
Quartz | Level 8

Thanks! Solved the problem!

sas-innovate-white.png

Our biggest data and AI event of the year.

Don’t miss the livestream kicking off May 7. It’s free. It’s easy. And it’s the best seat in the house.

Join us virtually with our complimentary SAS Innovate Digital Pass. Watch live or on-demand in multiple languages, with translations available to help you get the most out of every session.

 

Register now!

Creating Custom Steps in SAS Studio

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1595 views
  • 2 likes
  • 2 in conversation