topic 10 column to 5 column array in SAS Programming

10 column to 5 column array

eric_balke — Sun, 12 Jul 2015 17:25:57 GMT

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!

Re: 10 column to 5 column array

Reeza — Sun, 12 Jul 2015 18:47:12 GMT

Assuming that once a diagnosis field is empty all diagnosis fields afterwards is empty this logic works:

Create an array to hold diagnosis
Check if dx6 is missing, if missing then output and finished
If dx6 is not missing,
output record
move dx6-dx10 to dx1-dx5
output new record
Done

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;

Re: 10 column to 5 column array

slchen — Sun, 12 Jul 2015 19:01:09 GMT

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;

Re: 10 column to 5 column array

Jim_G — Sun, 12 Jul 2015 19:04:29 GMT

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;

drop dx1-dx10;

proc print; id claim new1-new5; run;

Re: 10 column to 5 column array

Tom — Sun, 12 Jul 2015 21:07:52 GMT

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;

Re: 10 column to 5 column array

Reeza — Sun, 12 Jul 2015 23:17:52 GMT

@Tom FTW