DATA Step, Macro, Functions and more

10 column to 5 column array

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

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
Super User
Posts: 6,495

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;

View solution in original post


All Replies
Super User
Posts: 17,748

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: 83

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
Super User
Posts: 6,495

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: 17,748

Re: 10 column to 5 column array

@Tom FTW Smiley Happy

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 348 views
  • 14 likes
  • 5 in conversation