topic Re: How do I count values within a variable multiple ways for a summary table in SAS EG? in SAS Enterprise Guide

How do I count values within a variable multiple ways for a summary table in SAS EG?

runningjay — Tue, 24 Jul 2018 15:18:26 GMT

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:

If the status is ft24 OR ft30 I would like to count it in the table column I've titled ft24+
If the status is ft30 I would like for it to be counted in the column I've titled ft30+
If the status is pt12 OR pt15 I would like to count it in the table column I've titled pt12+
If the status is pt15 I would like for it to be counted in the column I've titled pt15+

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!

Re: How do I count values within a variable multiple ways for a summary table in SAS EG?

novinosrin — Tue, 24 Jul 2018 15:28:38 GMT

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;

Re: How do I count values within a variable multiple ways for a summary table in SAS EG?

TomKari — Tue, 24 Jul 2018 15:59:41 GMT

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

Re: How do I count values within a variable multiple ways for a summary table in SAS EG?

runningjay — Tue, 24 Jul 2018 16:29:08 GMT

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 # # # # # # # #

program ft24+ ft30+ pt12+ pt15+

m f m f m f m f

a # # # # # # # #

b # # # # # # # #

Thanks!

Re: How do I count values within a variable multiple ways for a summary table in SAS EG?

novinosrin — Tue, 24 Jul 2018 16:32:14 GMT

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?

Re: How do I count values within a variable multiple ways for a summary table in SAS EG?

novinosrin — Tue, 24 Jul 2018 16:35:32 GMT

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;

Re: How do I count values within a variable multiple ways for a summary table in SAS EG?

TomKari — Tue, 24 Jul 2018 16:56:48 GMT

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

Re: How do I count values within a variable multiple ways for a summary table in SAS EG?

novinosrin — Tue, 24 Jul 2018 16:59:18 GMT

@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!