Help with Code

[robertrao]

Hi,

I learnt this technique from members recently, on how to transpose in the data step

I am trying to get one record per Admission discharge dates!!!

Can someone explain to me what the highlighted portion of the code is doing???????

Thanks

data have;

infile cards;

input IN :date9. OUT :date9.  NAME $  ADM_DATE :date9.   DIS_DATE :date9.    mrn  visit_ID;

datalines;

28AUG2011    29OCT2011    XYZ     28OCT2011    05DEC2011   101   1

01NOV2011    10NOV2011    XYZ     28OCT2011    05DEC2011   101   2

12NOV2011    25NOV2011    XYZ     28OCT2011    05DEC2011   101   3

12NOV2011    25NOV2011    aaa     29OCT2011    03DEC2011   101   1

run;

proc print data=have;

run;

%let maxnum=5;

proc sort data=have;

   by adm_date dis_date;

   run;

data want(keep= adm_date dis_date

                in1-in&maxnum

                out1-out&maxnum

                name1-name&maxnum

                mrn1-mrn&maxnum

                id1-id&maxnum);

   set have;

   by adm_date dis_date; 

   retain in1  - in&maxnum   .

          out1 - out&maxnum  .

          name1- name&maxnum '   '

          mrn1 - mrn&maxnum  .

          id1  - id&maxnum   .;

   array invals  {&maxnum} in1-in&maxnum;

   array outvals {&maxnum} out1-out&maxnum;

   array namvals {&maxnum} name1-name&maxnum;

   array mrnvals {&maxnum} mrn1-mrn&maxnum;

   array idvals  {&maxnum} id1-id&maxnum;

     if first.dis_date then do;

      * Clear arrays and retained variables;

      call missing(of invals{*},of outvals{*},of namvals{*},of mrnvals{*},of idvals{*});

      cnt=0;
  

   end;

  cnt+1;

   invals{cnt} = in;

   outvals{cnt}= out;

   namvals{cnt}= name;

   mrnvals{cnt}= mrn;

   idvals{cnt} = vsit_id;
putlog _all_;

   if last.dis_date then output want;

   run;

[KarlK · Accepted solution]

Sure. You're de-normalizing or "flattening" a normalized table. You're reading in multiple records (as many as &maxnum, or 5 in your example) by adm_date and dis_date, and storing the values of in, out, name, mrn and visit_id in similarly-named arrays. Then, when dis_date changes, you're writing out a flat record with adm_date, dis_date and 5 values of each of the other variables (some of which may be missing) renamed by appending the digits 1 to 5 to their original names.

Make sense?

Karl

[robertrao]

hI,

i HAVE YET ANOTHER QUESTION.

NAME ALSO HAS 5 VARIABLES NUMBERED NAME1--NAME5 IN THE SAME RECORD (HAVING THE SAME NAME AS THE NUMBER OF RECORDS PER ADM_DISCH)...I OUR CASE IT WILL BE 3 AND THE OTHER 2 WILL BE BLANK...

WHAT IF I WANT THE NAME TO BE WRITTEN ONLY ONCE INSTEAD OF 3 TIMES AND 2 BLANKS??????

tHANKS

[KarlK]

Just treat name like you do adm_date and dis_date. In other words:

1. Add name to your proc sort:

proc sort data=have;  by name adm_date dis_date; run;

2. In the keep statement for data want, get rid of:

name1-name&maxnum

and just keep name.

3. In data want, delete all references to name1-name&maxnum and the array namvals.

4. In data want, add name to the by statement just like in the proc sort.

That should do it.