topic Re: Need help in converting this long dataset to a wide one in SAS Programming

Need help in converting this long dataset to a wide one

SDasari2 — Tue, 20 Aug 2019 19:51:50 GMT

ID	DIAGNOSISCODELIST_ID	ISADMITDIAGNOSIS	DIAGNOSISCODE	PXEXTRACTDATETIME
CR-814420	1	1	J96.21	03MAY2019:00:00:22.000000
CR-814420	1	1	J96.21	11MAY2019:00:00:22.000000
CR-814420	2	.	J44.1	03MAY2019:00:00:22.000000
CR-814420	2	.	J44.1	11MAY2019:00:00:22.000000
CR-814420	3	.	A41.9	03MAY2019:00:00:22.000000
CR-814420	3	.	A41.9	11MAY2019:00:00:22.000000
CR-814420	4	.	J18.9	03MAY2019:00:00:22.000000
CR-814420	4	.	J18.9	11MAY2019:00:00:22.000000

I have a dataset like this and i need to covert it to 1 row per ID like the below. I need to take the max of the PXEXTRACTDATETIME for each ID/DIAGNOSISCODELIST_ID_1. Please advise the best way and i have also attached the example in an example.

Want

DIAGNOSISCODELIST_ID_1

ISADMITDIAGNOSIS_1

DIAGNOSISCODE_1

DIAGNOSISCODELIST_ID_2

ISADMITDIAGNOSIS_2

DIAGNOSISCODE_2

DIAGNOSISCODELIST_ID_3

ISADMITDIAGNOSIS_3

DIAGNOSISCODE_3

DIAGNOSISCODELIST_ID_4

ISADMITDIAGNOSIS_4

DIAGNOSISCODE_4

CR-814420

J96.21

J44.1

A41.9

J18.9

Re: Need help in converting this long dataset to a wide one

PaigeMiller — Tue, 20 Aug 2019 20:04:20 GMT

@SDasari2 wrote:

I have a dataset like this and i need to covert it to 1 row per ID like the below. I need to take the max of the PXEXTRACTDATETIME for each ID/DIAGNOSISCODELIST_ID_1. Please advise the best way and i have also attached the example in an example.

If this is your goal, there's no need to reformat the data at all. The maximum PXEXTRACTDATETIME can be found using PROC SUMMARY.

proc summary data=have nway;
    class id diagnosiscodelist_id;
    var pxextractdatetime;
    output out=want max=;
run;

For most analyses (not all, but most) reformatting a long data set to wide is an unnecessary and time consuming step.

Re: Need help in converting this long dataset to a wide one

ballardw — Tue, 20 Aug 2019 20:07:35 GMT

@SDasari2 wrote:

ID DIAGNOSISCODELIST_ID ISADMITDIAGNOSIS DIAGNOSISCODE PXEXTRACTDATETIME

CR-814420 1 1 J96.21 03MAY2019:00:00:22.000000

CR-814420 1 1 J96.21 11MAY2019:00:00:22.000000

CR-814420 2 . J44.1 03MAY2019:00:00:22.000000

CR-814420 2 . J44.1 11MAY2019:00:00:22.000000

CR-814420 3 . A41.9 03MAY2019:00:00:22.000000

CR-814420 3 . A41.9 11MAY2019:00:00:22.000000

CR-814420 4 . J18.9 03MAY2019:00:00:22.000000

CR-814420 4 . J18.9 11MAY2019:00:00:22.000000

I have a dataset like this and i need to covert it to 1 row per ID like the below. I need to take the max of the PXEXTRACTDATETIME for each ID/DIAGNOSISCODELIST_ID_1. Please advise the best way and i have also attached the example in an example.

Want

ID DIAGNOSISCODELIST_ID_1 ISADMITDIAGNOSIS_1 DIAGNOSISCODE_1 DIAGNOSISCODELIST_ID_2 ISADMITDIAGNOSIS_2 DIAGNOSISCODE_2 DIAGNOSISCODELIST_ID_3 ISADMITDIAGNOSIS_3 DIAGNOSISCODE_3 DIAGNOSISCODELIST_ID_4 ISADMITDIAGNOSIS_4 DIAGNOSISCODE_4

CR-814420 1 1 J96.21 2 . J44.1 3 . A41.9 4 . J18.9

Do you want the max or are you looking to "dedup" the records keeping the latest PXEXTRACTDATETIME record for each ID Diagnosiscodelist_id?

Re: Need help in converting this long dataset to a wide one

SDasari2 — Tue, 20 Aug 2019 20:16:09 GMT

I want the max(pxextractdatetime) for each ID/DIAGNOSISCODELIST_ID.

Re: Need help in converting this long dataset to a wide one

SDasari2 — Tue, 20 Aug 2019 20:22:51 GMT

This give the max of pxextractdatetime with the 4 differnt DIAGNOSISCODELIST_ID one below the other. I need all of them in one row for the given ID.

Re: Need help in converting this long dataset to a wide one

novinosrin — Tue, 20 Aug 2019 20:26:30 GMT

Hello @SDasari2

data have;
input ID :$30.	DIAGNOSISCODELIST_ID	ISADMITDIAGNOSIS	DIAGNOSISCODE :$15.	PXEXTRACTDATETIME :datetime40.;
format PXEXTRACTDATETIME datetime40.;
cards;
CR-814420	1	1	J96.21	03MAY2019:00:00:22.000000
CR-814420	1	1	J96.21	11MAY2019:00:00:22.000000
CR-814420	2	.	J44.1	03MAY2019:00:00:22.000000
CR-814420	2	.	J44.1	11MAY2019:00:00:22.000000
CR-814420	3	.	A41.9	03MAY2019:00:00:22.000000
CR-814420	3	.	A41.9	11MAY2019:00:00:22.000000
CR-814420	4	.	J18.9	03MAY2019:00:00:22.000000
CR-814420	4	.	J18.9	11MAY2019:00:00:22.000000
;

proc sql;
create table temp as
select * 
from have
group by ID,DIAGNOSISCODELIST_ID
having max(PXEXTRACTDATETIME)=PXEXTRACTDATETIME;
quit;

proc sql noprint; 
select max(obs) into :obs
from (select count(*) as obs  from temp group by ID ) ;
quit;
%put &=obs;

proc summary nway data=temp missing; 
 class ID ; 
 output 
 out = work.wide(drop=_type_ _freq_) 
 idgroup(out[&obs](DIAGNOSISCODELIST_ID ISADMITDIAGNOSIS DIAGNOSISCODE)=) 
 ;
run;

Re: Need help in converting this long dataset to a wide one

SDasari2 — Tue, 20 Aug 2019 23:04:48 GMT

Thanks a lot. This works!