BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
robertrao
Quartz | Level 8

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;

1 ACCEPTED SOLUTION

Accepted Solutions
KarlK
Fluorite | Level 6

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

View solution in original post

3 REPLIES 3
KarlK
Fluorite | Level 6

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
Quartz | Level 8

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
Fluorite | Level 6

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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 847 views
  • 3 likes
  • 2 in conversation