I have a set of data in SAS EG and would like to count certain values in two different columns when a certain condition is met. I'm not sure how SAS EG wizards can assist with this or if it would require code to be entered to generate the table.
For the output table I would like to count the "status" value in multiple columns (see Original data and Desired table below).
I would like the data to be summarized as follows by program:
Original data
program,id,status,gender
a,jk20020,ft24,m
a,rm5050,ft24,f
a,rn1010,ft30,m
a,nr80080,ft30,f
a,ab10010,pt12,m
a,zu99000,pt12,f
a,xx44100,pt12,f
a,be55333,pt15,m
a,qp22734,pt15,m
b,uv19356,ft24,f
b,pq34224,ft24,m
b,qz11222,ft30,f
b,ww44120,ft30,m
b,qq99018,ft30,f
b,rt33201,pt15,f
b,ww99222,pt15,f
b,zl5213,pt15,f
Desired output in table format
program ft24+ ft30+ pt12+ pt15+
a 4 2 5 2
b 5 3 3 3
Any insight and guidance is much appreciated by this SAS newbie!
data have;
infile cards dlm=',';
input program $id :$12. status $ gender $;
cards;
a,jk20020,ft24,m
a,rm5050,ft24,f
a,rn1010,ft30,m
a,nr80080,ft30,f
a,ab10010,pt12,m
a,zu99000,pt12,f
a,xx44100,pt12,f
a,be55333,pt15,m
a,qp22734,pt15,m
b,uv19356,ft24,f
b,pq34224,ft24,m
b,qz11222,ft30,f
b,ww44120,ft30,m
b,qq99018,ft30,f
b,rt33201,pt15,f
b,ww99222,pt15,f
b,zl5213,pt15,f
;
proc sql;
create table want as
select program, sum(status in ('ft24','ft30')) as ft24,sum(status='ft30') as ft30,sum(status in ('pt12','pt15')) as pt12,sum(status='pt15') as pt15
from have
group by program;
quit;
data have;
infile cards dlm=',';
input program $id :$12. status $ gender $;
cards;
a,jk20020,ft24,m
a,rm5050,ft24,f
a,rn1010,ft30,m
a,nr80080,ft30,f
a,ab10010,pt12,m
a,zu99000,pt12,f
a,xx44100,pt12,f
a,be55333,pt15,m
a,qp22734,pt15,m
b,uv19356,ft24,f
b,pq34224,ft24,m
b,qz11222,ft30,f
b,ww44120,ft30,m
b,qq99018,ft30,f
b,rt33201,pt15,f
b,ww99222,pt15,f
b,zl5213,pt15,f
;
proc sql;
create table want as
select program, sum(status in ('ft24','ft30')) as ft24,sum(status='ft30') as ft30,sum(status in ('pt12','pt15')) as pt12,sum(status='pt15') as pt15
from have
group by program;
quit;
If I wanted to continue to group by program but also include the gender variable by column output how could this code be adjusted to accomplish that?
For example if I wanted the same results, but I wanted it to look like this (either with gender concatenated into the column name or with gender falling under each category):
program ft24m ft24f ft30m ft30f pt12m pt12f pt15m pt15f
a # # # # # # # #
b # # # # # # # #
OR
program ft24+ ft30+ pt12+ pt15+
m f m f m f m f
a # # # # # # # #
b # # # # # # # #
Thanks!
do you mean group by program gender;
depends upon what you want in your results
EDIT: do you mean count of gender: m and f?
data have;
infile cards dlm=',';
input program $id :$12. status $ gender $;
cards;
a,jk20020,ft24,m
a,rm5050,ft24,f
a,rn1010,ft30,m
a,nr80080,ft30,f
a,ab10010,pt12,m
a,zu99000,pt12,f
a,xx44100,pt12,f
a,be55333,pt15,m
a,qp22734,pt15,m
b,uv19356,ft24,f
b,pq34224,ft24,m
b,qz11222,ft30,f
b,ww44120,ft30,m
b,qq99018,ft30,f
b,rt33201,pt15,f
b,ww99222,pt15,f
b,zl5213,pt15,f
;
proc sql;
create table want as
select program, sum(status in ('ft24','ft30')) as ft24,sum(status='ft30') as ft30,sum(status in ('pt12','pt15')) as pt12,sum(status='pt15') as pt15,sum(gender='m') as m_count, sum(gender='f') as f_count
from have
group by program;
quit;
In your Summary Tables step, just drag gender under the status variable in the design window, as shown at the left. The results will be as shown on the right.
Tom
@TomKari simply awesome. @runningjay You should switch to Tom's solution. That seems eloquent and neat. If you do so, make sure you switch the credit to him as well. Thank you!
Another really good option is that SAS has a facility called "multi-label formats", where a value can have more than one label assigned.
Unfortunately, I don't think you can create one using the EG tasks to create a format, but if you run this code:
proc format;
value $Statf (multilabel)
"ft24" = "ft24+"
"ft30" = "ft24+"
"ft30" = "ft30+"
"pt12" = "pt12+"
"pt15" = "pt12+"
"pt15" = "pt15+"
;
run;
and then assign the format to your variable, you can get your desired results in the "Summary Tables" task.
On the "Data" tab of your summary table setup, with the status variable selected, change "Multi-label formats" from Disabled to Enabled. When you create your table, "ft30" should be included in both "ft24+" and "ft30+".
Tom
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!
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.