So I have two fields, name and type and I am trying to figure how to break this out into more distinct columns.
Name has just different names of users
Type has different colors.
So what I am trying to do is:
Name Count of Red Count of Blue Count of Green
Matt 4 1 2
Ben 2 3 0
Frank 6 4 1
You can do that with proc freq and proc transpose:
data have;
input name $ type $;
cards;
Matt Red
Matt Red
Matt Red
Matt Red
Matt Blue
Matt Green
Matt Green
;
run;
proc freq data=have;
by name;
table type/ out=inter nocum nocol nopercent;
run;
proc transpose data=inter out=want (drop=_name_ _label_) prefix=count_of_;
by name;
id type;
var count;
run;
proc print data=want noobs;
run;
Result:
count_ count_ count_ name of_Blue of_Green of_Red Matt 1 2 4
Proc freq your starting data, then transpose the output from the proc freq. Post test data in the form of a datastep if you want actually working code, I am not here to type in test data.
You can do that with proc freq and proc transpose:
data have;
input name $ type $;
cards;
Matt Red
Matt Red
Matt Red
Matt Red
Matt Blue
Matt Green
Matt Green
;
run;
proc freq data=have;
by name;
table type/ out=inter nocum nocol nopercent;
run;
proc transpose data=inter out=want (drop=_name_ _label_) prefix=count_of_;
by name;
id type;
var count;
run;
proc print data=want noobs;
run;
Result:
count_ count_ count_ name of_Blue of_Green of_Red Matt 1 2 4
Do you want a data set (used by computer for other processes) or report (people read it);
One report
Proc tabulate data=have;
class name color;
table name,
color
;
run;
I appreciate all the responses on this. Not used proc transpose a whole lot so this was interesting to see work.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.