Desktop productivity for business analysts and programmers

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

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

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:

  1. If the status is ft24 OR ft30 I would like to count it in the table column I've titled ft24+
  2. If the status is ft30 I would like for it to be counted in the column I've titled ft30+
  3. If the status is pt12 OR pt15 I would like to count it in the table column I've titled pt12+
  4. 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!


Accepted Solutions
Solution
a month ago
Super User
Posts: 2,068

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

Posted in reply to runningjay
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;

View solution in original post


All Replies
Solution
a month ago
Super User
Posts: 2,068

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

Posted in reply to runningjay
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;
Occasional Contributor
Posts: 15

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

Posted in reply to novinosrin

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!

 

Super User
Posts: 2,068

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

[ Edited ]
Posted in reply to runningjay

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?

Super User
Posts: 2,068

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

Posted in reply to novinosrin

 

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;

PROC Star
Posts: 1,334

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

Posted in reply to runningjay

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

 

EGPic.png

Super User
Posts: 2,068

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

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

PROC Star
Posts: 1,334

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

Posted in reply to runningjay

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

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 146 views
  • 1 like
  • 3 in conversation