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
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.