topic Transposing with an array in SAS Programming

Transposing with an array

rlafond — Fri, 29 Mar 2019 14:14:11 GMT

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?

Re: Transposing with an array

novinosrin — Fri, 29 Mar 2019 14:16:45 GMT

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

Re: Transposing with an array

rlafond — Fri, 29 Mar 2019 14:18:41 GMT

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

Re: Transposing with an array

data_null__ — Fri, 29 Mar 2019 14:52:25 GMT

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

Re: Transposing with an array

VDD — Fri, 29 Mar 2019 14:36:22 GMT

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/

Re: Transposing with an array

novinosrin — Fri, 29 Mar 2019 14:40:40 GMT

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;

Re: Transposing with an array

rlafond — Fri, 29 Mar 2019 14:45:01 GMT

Thanks!

Re: Transposing with an array

data_null__ — Fri, 29 Mar 2019 16:56:03 GMT

@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?

Re: Transposing with an array

novinosrin — Fri, 29 Mar 2019 17:10:52 GMT

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!