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?
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;
Can you please post what you want as output for the sample input?
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 |
when you reset "I", also use CALL MISSING on the array
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/
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;
Thanks!
@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?
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!
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.