Transposing with an array

[rlafond]

I have a long data set. Here is a sample:

ID	BaseEventDate	Diag1	SURG_IND	Discharge_CC
1	7/22/2018	K353	N
2	7/5/2018	E1152	N	CC-106
2	7/5/2018	E1152	N	CC-108
2	7/5/2018	E1152	N	CC-18
3	3/13/2018	A419	N	CC-2

I'm using an array to make it wide based on the Discharge_CC column. Each unique instance of ID and BaseEventDate should have a record.

Here is my SAS code:

DATA STEP6;

SET WORK.STEP5;

BY ID BaseEventDate;

ARRAY dcc (3) $ DCC1-DCC3;

IF FIRST.ID or FIRST.BaseEventDate THEN i=1;

dcc(i) = DISCHARGE_CC;

retain dcc1-dcc3;

if last.BaseEventDate or last.ID then output;

i+1;

drop DISCHARGE_CC i;

run;

But here is my output:

ID	BaseEventDate	Diag1	SURG_IND	DCC1	DCC2	DCC3
1	7/22/2018	K353	N
2	7/5/2018	E1152	N	CC-106	CC-108	CC-18
3	3/13/2018	A419	N	CC-2	CC-108	CC-18

I don't get why DCC2 and DCC3 from ID 2 are being retained with ID 3. Any ideas?

[novinosrin]

Can you please post what you want as output for the sample input?

[rlafond]

Sure. This is what I want to see:

ID	BaseEventDate	Diag1	SURG_IND	DCC1	DCC2	DCC3
1	7/22/2018	K353	N
2	7/5/2018	E1152	N	CC-106	CC-108	CC-18
3	3/13/2018	A419	N	CC-2

[data_null__]

when you reset "I", also use CALL MISSING on the array

[VDD]

this line of code is what is creating your issue.

retain dcc1-dcc3;

you need to reset dcc1-dcc3 after you output the record.

here is a link to transpose using proc transpose which will give you what you want.

https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/

[novinosrin · Accepted solution]

Guru @data_null__  (@Guru, you have been busy lately? I haven't seen you here much) has answered. Let me complete it

data have;
input ID	BaseEventDate :mmddyy10.	(Diag1	SURG_IND	Discharge_CC) ($);
format BaseEventDate mmddyy10.
cards;
1	7/22/2018	K353	N	.
2	7/5/2018	E1152	N	CC-106
2	7/5/2018	E1152	N	CC-108
2	7/5/2018	E1152	N	CC-18
3	3/13/2018	A419	N	CC-2
;
proc sql;
select max(c) into :m
from (select count(id) as c from have group by id);
quit;

data want;
do _n_=1 by 1 until(last.BaseEventDate);
set have;
by ID	BaseEventDate;
array DCC(&m)$;
DCC(_n_)=Discharge_CC;
end;
drop Discharge_CC;
run;

[rlafond]

Thanks!

[data_null__]

---

@novinosrin wrote:

Guru @data_null__  (@Guru, you have been busy lately? I haven't seen you here much) has answered. Let me complete it

run;

---

I'm still around most days.  Busy with work and most questions are answered before I read them.  You going to SGF?

[novinosrin]

Thank you Guru @data_null__  for the message. I wanted to and there was indeed a plan to attend SGF. But since, I am just transitioning from school to career having completed my Master's last week with a GPA of 3.6 majoring in Information systems with a concentration in Business intelligence, I couldn't get it to work. I even sent you an invite on PM(private message) for the graduation ceremony to be held in June, 2019 via linkedin. 

Anyways, so yeah, Because of the transition that I have taken a couple of interviews and with the hope that should be successful, I might have to move away from Chicago or even if within Chicago-land, it's likely in the suburb. All of this is making it to difficult to to be continuously engaged with our community or SGF for that matter. I will PM more details once I have something for real.

Paul D the Hashman is apparently presenting and he invited me too for a coffee/lunch alongside the event and you know, I so want to meet you and him and a couple others whom I dearly follow and learn right from your posts on SAS-L and of course here and beyond i.e your papers. Well yeah, once I kinda settle down, I so want to get truly acquainted in person with you SAS GEMS.  Thank you as always!