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;
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
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
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.