## 10 column to 5 column array

Solved
Occasional Contributor
Posts: 6

# 10 column to 5 column array

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!

Accepted Solutions
Solution
‎07-12-2015 05:07 PM
Super User
Posts: 8,093

## Re: 10 column to 5 column array

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;

All Replies
Super User
Posts: 23,700

## Re: 10 column to 5 column array

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

1. Create an array to hold diagnosis
2. Check if dx6 is missing, if missing then output and finished
3. If dx6 is not missing,
4.     output record
5.     move dx6-dx10 to dx1-dx5
6.     output new record
7. 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;

Super Contributor
Posts: 275

## Re: 10 column to 5 column array

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;

Frequent Contributor
Posts: 102

## Re: 10 column to 5 column array

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;

Solution
‎07-12-2015 05:07 PM
Super User
Posts: 8,093

## Re: 10 column to 5 column array

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;

Super User
Posts: 23,700

## Re: 10 column to 5 column array

@Tom FTW

🔒 This topic is solved and locked.