topic obtaining freq efficiently from columns in SAS Programming

obtaining freq efficiently from columns

jcooper3 — Thu, 06 Feb 2014 20:25:01 GMT

Hi all-

This is my following dataset:

patient ID qtr1 qtr2 qtr3 qtr4
1 7 7 7 0

1 0 0 8 8

There are ~50,000 patients and each value under the quarters columns (there are 36 quarters) correspond to a specific medication.

I want to obtain the frequencies of what the numbers are under each column. So how many people are taking drug=7, drug=9 (and so on) within each quarter.

PROC FREQ doesn't seem to be doing a great job at this, is there another option? help! thanks!!

Re: obtaining freq efficiently from columns

ballardw — Thu, 06 Feb 2014 20:28:11 GMT

It would help to provide a few more rows of dummy data and what the desired output for that data would be. There are several ways to interpret your request.

Also what did you do with Freq and what was "not a great job". You may have been close.

Re: obtaining freq efficiently from columns

jcooper3 — Thu, 06 Feb 2014 20:35:32 GMT

hi ballardw-

I included another patient:

patient ID qtr1 qtr2 qtr3 qtr4
1 7 7 7 0

1 0 0 8 8

2 7 7 7 7

2 0 0 0 0

There are usually multiple observations per patients because drugs show up throughout the study period (which is 36 quarters). I'm ultimately wanting to graph this data. I want the percentage of patients on drug 7, drug 8, drug 9 (and so on) within each quarter over 36 quarters.

I wrote this just to see what was going on qtr 1 and it took a while- hence, why I was wondering if there was a more efficient way of doing this or if I'm completely on the wrong track all together.

proc freq data= xx;

tables qtr1;

where qtr1 ge 1;

run;

Re: obtaining freq efficiently from columns

ballardw — Fri, 07 Feb 2014 00:07:49 GMT

I am starting think that you may need to reshape your data if the 7, 8, etc are drug codes.

Something like:

data want (keep=PatientId Quarter DrugCode); /*I'm guessing as to actual variable name of your patient id variable*/

set xx;

array q qtr: ;

do _i_ = 1 to dim(q);

Quarter = _i_; /* this will be the number of the quarter*/

if q[_i_] > 0 then do; /* if your code values within the quarters are not numeric we'll have to work on this*/

DrugCode = q[_i_];

output;

end;

run;

proc freq data=want;

tables DrugCode * Quarter;

run;

By looking at the row, colomn and overall percents give either overall or within quarter as well as percentage of quarter receiving drug.