topic Re: Counting frequency by group and year in SAS Programming

Counting frequency by group and year

jgreenberg321 — Sun, 17 May 2020 22:35:58 GMT

I am working with a large dataset that contains data for many hospitals (marked by "ahaid") across several years (2010-2015). Year is marked by "pryear." Each row indicates a unique surgical visit. I am trying to calculate the number of surgical visits per hospital for each year. My goal is to output a new dataset that contains "ahaid," "pryear," and count variables for each year (count_10, count_11, count_12...). I want the count variables to represent the total number of surgical visits for that value of ahaid for the specified year. I believe I can combine do loops and "by" statements to accomplish this, but am not sure of exactly the right sequence. Can anyone help?

Thank you very much!

Re: Counting frequency by group and year

PaigeMiller — Mon, 18 May 2020 00:41:06 GMT

Use PROC FREQ instead of do loops.

proc freq data=have;
    tables ahaid*pryear;
run;

Re: Counting frequency by group and year

jgreenberg321 — Mon, 18 May 2020 00:55:26 GMT

Thank you.

However I need to output the frequency values as new variables (one value per ahaid per pryear). Can I do that with the proc freq approach?

Thank you again!

Re: Counting frequency by group and year

CarlosSpranger — Mon, 18 May 2020 02:43:34 GMT

Hello @jgreenberg321 ,

I was playing with your challenge and I have the following idea in attach

sas_snippet_cs_to_jgreenberg321.sas

Regards,

Re: Counting frequency by group and year

PaigeMiller — Mon, 18 May 2020 10:41:50 GMT

@jgreenberg321 wrote:

However I need to output the frequency values as new variables (one value per ahaid per pryear). Can I do that with the proc freq approach?

Yes. Use the PROC FREQ options named LIST and OUT=

proc freq data=have;
    tables ahaid*pryear/list out=want noprint;
run;

Re: Counting frequency by group and year

jgreenberg321 — Mon, 18 May 2020 13:36:27 GMT

Thank you! This seems to work well, though I expect the other solution proposed below would also work.

To organize my results by number of cases per year, I just used a transpose statement on the output dataset, though other solutions could have been as efficient or better. Thank you all for your help!

proc freq data=have;

table ahaid*pryear/list out=want_prelim (drop=percent) noprint;

run;

proc transpose data=want_prelim out=want_final (drop=_NAME_ _LABEL_);

by ahaid;

id pryear;

run;

Re: Counting frequency by group and year

jgreenberg321 — Mon, 18 May 2020 20:34:17 GMT

Thank you!