BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
runningjay
Fluorite | Level 6

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!

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20
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

7 REPLIES 7
novinosrin
Tourmaline | Level 20
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;
runningjay
Fluorite | Level 6

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!

 

novinosrin
Tourmaline | Level 20

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?

novinosrin
Tourmaline | Level 20

 

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;

TomKari
Onyx | Level 15

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

novinosrin
Tourmaline | Level 20

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

TomKari
Onyx | Level 15

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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