Hello,
I am trying to convert 10 dx columns per claim into 5 dx columns per claim, duplicating claims when dx's are greater than 5;
The data I have is:
claim dx1 dx2 ...dx6....dx10
A V22 V23....V24.......
What I want is:
claim dx1 dx2 dx3 dx4 dx5
A V22 V23 ...................
A V24
I am having trouble getting the array statements to work properly.
Thanks!
Assuming that values are left aligned into the array you can do it with data set options.
data have ;
length id 8 dx1-dx10 $5 ;
input id dx1-dx10;
cards;
1 1 2 3 4 5 6 7 8 9 10
2 1 2 3 4 . . . . . .
;;;;
data want ;
set have (drop=dx6-dx10)
have (in=in2 drop=dx1-dx5 rename=(dx6-dx10=dx1-dx5) where=(not missing(dx1)))
;
by id ;
extra = in2 ;
run;
proc print; run;
Assuming that once a diagnosis field is empty all diagnosis fields afterwards is empty this logic works:
Also, I can't visualize a scenario when this would ever be a good way to store data.
data want;
set have;
array dx_orig(10) dx1-dx10;
if not missing(dx(6)) then do;
output;
do i=6 to 10;
dx(i-5)=dx(i);
end;
output;
end;
else output;
drop dx6-dx10;
run;
data want;
set have;
array vars1 dx1-dx5;
array vars2 dx6-dx10;
call missing(of vars2(*));
output;
set have;
call missing (of vars1(*));
do over vars1;
vars1=vars2;
end;
output;
drop dx6-dx10;
run;
Look at the 10 dx's. when you find non blank one put it in the output array.
Data one; infile cards dsd;
input claim $7. (dx1-dx10) (:$8.);
cards;
2222112 v22, v22, v24,,,,,v13,,
2222543 v22, , , v33,,,,,v13,,
proc print; run;
data want; set;
array dx dx1-dx10 ;
array new $8 new1-new5 ;
y=0;
do x=1 to 10;
if dx{x} ne ' ' then do;
Y+1; new{y}=dx{x};
end;
end;
drop dx1-dx10;
proc print; id claim new1-new5; run;
Assuming that values are left aligned into the array you can do it with data set options.
data have ;
length id 8 dx1-dx10 $5 ;
input id dx1-dx10;
cards;
1 1 2 3 4 5 6 7 8 9 10
2 1 2 3 4 . . . . . .
;;;;
data want ;
set have (drop=dx6-dx10)
have (in=in2 drop=dx1-dx5 rename=(dx6-dx10=dx1-dx5) where=(not missing(dx1)))
;
by id ;
extra = in2 ;
run;
proc print; run;
@Tom FTW
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.