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

Hello - I would like to understand how to solve the below using select and subselect query

 

  1. count all desy_sort_keys where clm_drg_cd = '469'
  2. count all desy_sort_keys where clm_drg_cd = '469' and age_range = '64 and younger'
  3. select the count from item 2 and divide it by item 1 creating a percentage in the format of 99.9%

 

proc sql ;
title '64 and younger & 469';
select count(desy_sort_key) from etl.inp_claims_lds2013_2016 where
Age_Range='64 and younger' and clm_drg_cd='469';
quit;

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

Sounds like a good problem for PROC FREQ. 

 

proc freq data=etl.inp_claims.... ;
where clm_drg_cd='469';
table age_range*desy_sort_key;
run;

 

 


@fpascal wrote:

Hello - I would like to understand how to solve the below using select and subselect query

 

  1. count all desy_sort_keys where clm_drg_cd = '469'
  2. count all desy_sort_keys where clm_drg_cd = '469' and age_range = '64 and younger'
  3. select the count from item 2 and divide it by item 1 creating a percentage in the format of 99.9%

 

proc sql ;
title '64 and younger & 469';
select count(desy_sort_key) from etl.inp_claims_lds2013_2016 where
Age_Range='64 and younger' and clm_drg_cd='469';
quit;


 

View solution in original post

9 REPLIES 9
Reeza
Super User

Sounds like a good problem for PROC FREQ. 

 

proc freq data=etl.inp_claims.... ;
where clm_drg_cd='469';
table age_range*desy_sort_key;
run;

 

 


@fpascal wrote:

Hello - I would like to understand how to solve the below using select and subselect query

 

  1. count all desy_sort_keys where clm_drg_cd = '469'
  2. count all desy_sort_keys where clm_drg_cd = '469' and age_range = '64 and younger'
  3. select the count from item 2 and divide it by item 1 creating a percentage in the format of 99.9%

 

proc sql ;
title '64 and younger & 469';
select count(desy_sort_key) from etl.inp_claims_lds2013_2016 where
Age_Range='64 and younger' and clm_drg_cd='469';
quit;


 

fpascal
Quartz | Level 8
Reeza - my data set has only 74K obs. For the above solution it is still running. Is that normal?
Reeza
Super User

@fpascal wrote:
Reeza - my data set has only 74K obs. For the above solution it is still running. Is that normal?

No. 

 

Did you terminate it correctly with a RUN;?

 

 

fpascal
Quartz | Level 8

Reeza

 

The output finally came back - its attached. The problem is I wanted a count(#) for each age group by counting desy_sort_key.  How would I modify the proc frequency code?

Reeza
Super User

That's what it did, you just happen to have a lot of 0's. 

fpascal
Quartz | Level 8
Reeza


This is the table format I was looking for



Count(Desy_sort_key)


Age Range


64 and younger 99.9% (percentage of the total count of desy_sort_key for 469)


70 to 74 99.9% (percentage of the total count of desy_sort_key for 469)


75 to 79 99.9% (percentage of the total count of desy_sort_key for 469)


80 to 84 99.9% (percentage of the total count of desy_sort_key for 469)


85 and older 99.9% (percentage of the total count of desy_sort_key for 469)


Reeza
Super User

Format is a different issue - all the values you need are there. I would suggest creating an output table then instead or a listed table. You should review the options available for PROC FREQ in the documentation. 

 

See the example below

 

proc freq data=sashelp.cars noprint;
table origin*cylinders / out=summary outpct;
run;

@fpascal wrote:
Reeza


This is the table format I was looking for



Count(Desy_sort_key)


Age Range


64 and younger 99.9% (percentage of the total count of desy_sort_key for 469)


70 to 74 99.9% (percentage of the total count of desy_sort_key for 469)


75 to 79 99.9% (percentage of the total count of desy_sort_key for 469)


80 to 84 99.9% (percentage of the total count of desy_sort_key for 469)


85 and older 99.9% (percentage of the total count of desy_sort_key for 469)



 

Reeza
Super User

Note that I can't see your data so I'm making assumptions. It's possible they're wrong, so feel free to test out different options as needed and play around with the different table structures. See the SAS PROC FREQ documentation for examples and the list of options available.

 

fpascal
Quartz | Level 8
Reeza - thanks for pushing me to work out. You were right. I used the code below to get the look and field I wanted. took out the '*' - Thanks again.


proc freq data=etl.inp_claims_lds2013_2016 ;
where clm_drg_cd='469';
table age_range desy_sort_key;
run;

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!

How to Concatenate Values

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.

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
  • 9 replies
  • 1212 views
  • 0 likes
  • 2 in conversation