obtaining freq efficiently from columns

Reply
Occasional Contributor
Posts: 12

obtaining freq efficiently from columns

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

Super User
Posts: 10,538

Re: obtaining freq efficiently from columns

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.

Occasional Contributor
Posts: 12

Re: obtaining freq efficiently from columns

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

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;

Super User
Posts: 10,538

Re: obtaining freq efficiently from columns

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;

     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.

Ask a Question
Discussion stats
  • 3 replies
  • 195 views
  • 0 likes
  • 2 in conversation