Solved
Contributor
Posts: 44

# Select and subselect

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;

Accepted Solutions
Solution
‎03-13-2018 03:31 PM
Super User
Posts: 23,754

## Re: Select and subselect

[ Edited ]

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;

All Replies
Solution
‎03-13-2018 03:31 PM
Super User
Posts: 23,754

## Re: Select and subselect

[ Edited ]

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;

Contributor
Posts: 44

## Re: Select and subselect

Reeza - my data set has only 74K obs. For the above solution it is still running. Is that normal?
Super User
Posts: 23,754

## Re: Select and subselect

@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;?

Contributor
Posts: 44

## Re: Select and subselect

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?

Super User
Posts: 23,754

## Re: Select and subselect

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

Contributor
Posts: 44

## Re: Select and subselect

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)

Super User
Posts: 23,754

## Re: Select and subselect

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)

Super User
Posts: 23,754

## Re: Select and subselect

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.

Contributor
Posts: 44

## Re: Select and subselect

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;

☑ This topic is solved.