topic Proc transpose in SAS Programming

Proc transpose

chuakp — Wed, 23 Dec 2015 18:21:46 GMT

I have a large claims databases with four diagnosis codes per claim. The data looks like this:

ID CLAIM_ID DATE DX1 DX2 DX3 DX4

1 100 1/1/2015 7804

1 101 1/1/2015 30921 39021

1 102 2/1/2015 30943 902 01920

1 103 3/1/2015 011

2 104 4/1/2015 4530

2 105 5/1/2015 V9090

3 106 6/1/2015 7039

3 107 6/1/2015 7039

3 108 6/1/2015 E884 0930 1092 0930

3 109 7/1/2015 3094

data have;

infile datalines dlm="," missover;

input id claim_id date $ dx1 $ dx2 $ dx3 $ dx4 $;

datalines;

1,100,1/1/2015,7804,,

1,101,1/1/2015,30921,39021,,

1,102,2/1/2015,30943,902,01920,

1,103,3/1/2015,011,,,

2,104,4/1/2015,4530,,,

2,105,5/1/2015,V9090,,,

3,106,6/1/2015,7039,,,

3,107,6/1/2015,7039,,,

3,108,6/1/2015,E884,0930,1092,0930

3,109,7/1/2015,3094,,,

;

run;

I want to create a dataset that has all of the *unique* diagnosis codes that occurred on claims during the same day for a given individual, as below (I don't care about whether it was DX1, DX2, DX3, or DX4). This would involve using proc transpose somehow to create a series of variables that I might call "DXS1-DXS5" (in this fake example, five variables would be created but it would in reality be way more). I've been playing with the syntax of proc transpose and can't get this work, though.

ID CLAIM_ID DATE DX1 DX2 DX3 DX4 DXS1 DXS2 DXS3 DXS4 DXS5

1 100 1/1/2015 7804 7804 30921 39021

1 101 1/1/2015 30921 39021 7804 30921 39021

1 102 2/1/2015 30943 902 01920 30943 902 01920

1 103 3/1/2015 011 011

2 104 4/1/2015 4530 4530

2 105 5/1/2015 V9090 V9090

3 106 6/1/2015 7039 7039 E884 0930 1092 0930

3 107 6/1/2015 7039 7039 E884 0930 1092 0930

3 108 6/1/2015 E884 0930 1092 0930 7039 E884 0930 1092 0930

3 109 7/1/2015 3094 3094

I'd appreciate suggestions on how to proceed. Thanks.

Re: Proc transpose

Reeza — Wed, 23 Dec 2015 18:32:05 GMT

Your moving diagnosis from one record to another because its the same day? What's the point of keeping multiple records per day?

Re: Proc transpose

Reeza — Wed, 23 Dec 2015 18:38:46 GMT

You need to calculate the daily separately and then merge it back in.

data have;
  infile datalines dlm="," truncover;
  input id claim_id date $ dx1 $ dx2 $ dx3 $ dx4 $;
datalines;
1,100,1/1/2015,7804,,  
1,101,1/1/2015,30921,39021,,
1,102,2/1/2015,30943,902,01920,
1,103,3/1/2015,011,,,
2,104,4/1/2015,4530,,,
2,105,5/1/2015,V9090,,,
3,106,6/1/2015,7039,,,
3,107,6/1/2015,7039,,,
3,108,6/1/2015,E884,0930,1092,0930
3,109,7/1/2015,3094,,,
;
run;

proc transpose data=have out=flipped(rename=col1=dx);
by id claim_id date;
var dx1-dx4;
run;

proc sort data=flipped nodupkey out=flipped_unique (where=(not missing(dx)) drop=claim_id);
by id date dx;
run;

data id;
set flipped_unique;
by id date;
if first.date then count=1; else count+1;
name=cats("dxs", count);
run;

proc transpose data=id out=wide_daily(drop=_name_);
by id date;
id name;
var dx;
run;

proc sql;
create table want as
select a.*, b.*
from have as a
left join wide_daily as b
on a.id=b.id and a.date=b.date;
quit;

Re: Proc transpose

Astounding — Wed, 23 Dec 2015 18:41:09 GMT

I would recommend that you first create a data set of all the DXS values, and then merge it back in. Here is one way (assuming DX1-DX4 are character):

data dxs;

set have;

array dx {4};

do i=1 to 4;

if dx{i} > ' ' then do;

diagnosis = dx{i};

output;

end;

keep ID diagnosis date;

run;

proc sort data=dxs nodupkey;

by id date diagnosis;

run;

At this point, you can transpose the unique diagnosis values back into a single observation per ID, with as many DXS variables as needed:

proc transpose data=dxs prefix='dxs' out=dxs_per_id (drop=_name_);

var diagnosis;

by id date;

run;

Then all that is left to do is to merge it back in. Assuming your original data set is sorted:

data want;

merge have dxs_per_id;

by id date;

run;

The code is untested, but should be fine. Write back if it causes any problems.

Good luck.

Re: Proc transpose

chuakp — Wed, 23 Dec 2015 21:16:19 GMT

Thank you. This works.

One last question - how would I create a global macro variable that has the number of DXS diagnoses in the file?

Re: Proc transpose

Astounding — Wed, 23 Dec 2015 21:23:23 GMT

There are a few ways ... here's one that meshes well with your program:

data _null_;

set dxs_per_id;

array dxs {*} dxs:;

call symputx('n_dxs_vars', dim(dxs));

stop;

run;

I think the best place to put this would be right after PROC TRANSPOSE creates DXS_PER_ID. It would be important to call the variable that you are transposing DIAGNOSIS (or at least any name that does not begin with DXS). The list DXS: is actually all variable names that begin with "DXS".

Re: Proc transpose

chuakp — Sat, 26 Dec 2015 15:23:45 GMT

Thanks so much.