Take some time to define how you want your output data to look like and all potential edge cases. It's much easier to get where you want to go and much easier to make suggestions. The way your question is currently formulated we'll have to guess at outputs, as well as it's restricted to people who have dealt with this exact scenario. If you phrase it in terms of a programming problem you'll get better responses. ... View more

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. ... View more

Another way to identify is to convert them into strings: data want; set have; array qtr quarter:; if find(cats(of qtr(*)),'11'); run; Haikuo ... View more

Something like: data temp;      set have;      by PatientId notsorted; /* if data is sorted by PatientId don't need the notsorted but assumes all the records for PatientId are together*/      if first.PatientId then do quarter = 1 to 36;           output;      end; run; proc sql;        create table want as      select a.*, b.*      from temp as a left join have as b on           a.PatientId = b.PatientId; quit; You'll get a warning about PatientId and quarter being in both tables but b.* is shorter than listing all the variables in Have except PatientId and quarter. ... View more