BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
eric_balke
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

5 REPLIES 5
Reeza
Super User

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;

slchen
Lapis Lazuli | Level 10

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;

Jim_G
Pyrite | Level 9

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; 

Tom
Super User Tom
Super User

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;

Reeza
Super User

@Tom FTW Smiley Happy

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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