BookmarkSubscribeRSS Feed
einstein
Quartz | Level 8

I have a data set which contains patients and the list of their procedures, but some of a patient's first procedure codes are missing.  Is there a way to shift the data over?

 

So the data looks like this:

 

Patient ID      Proc1    Proc2    Proc3    Proc4

12345           42.1       56.2      22.1      33.4

23456           42.1       56.2      22.1      33.4

34567           .                .         22.1      33.4

 

And I want the data for the last patient Id to have their procedure codes shift over so that the first procedure codes are not missing:

 

Patient ID      Proc1    Proc2    Proc3    Proc4

12345           42.1       56.2      22.1      33.4

23456           42.1       56.2      22.1      33.4

34567           22.1       33.4

 

Does anyone know how to do this?

 

Thanks!

8 REPLIES 8
Reeza
Super User

Is there any additional logic that needs to be considered?

Does it need to be a straight reassignment, ie proc3 to proc1 or can proc4 go to proc1?

 

Can you have all missing values?

 

 

 

 

einstein
Quartz | Level 8

Yes, whatever the first non-missing value is should be reassigned to the first missing value.  So yes, if proc1-proc3 are missing, but proc4-proc6 are not then proc4 becomes proc1 and proc5 becomes proc2, etc.

 

And yes, there are patients with all missing values in which case nothing happens to them.

Reeza
Super User

This won't work if you have skips - ie proc1 has a value, proc2 is missing, proc3 has a value. 

 

Otherwise this is simple brute force method to move the values using an array. 

data have;
infile cards truncover;
input Patient_ID $     Proc1    Proc2    Proc3    Proc4;
cards;
12345 42.1 56.2 22.1 33.4
23456 42.1 56.2 22.1 33.4
34567 .      .  22.1 33.4
;
run;

data want;
set have;
array proc(4) proc1-proc4;

do i=1 to 4;
	if proc(i) ne .  then leave;
end;

*move values to missing records;
if i ne 1 then do j=1 to dim(proc)-i+1;
	proc(j)=proc(i);
	proc(i)=.;
	i=i+1;
end;

drop i j;

run;
einstein
Quartz | Level 8

Ah!  This worked!  Thanks!

FreelanceReinh
Jade | Level 19

It's good that PROCi are character variables because this avoids numeric representation issues. I assume that you adapted Reeza's code correspondingly (i.e. used ' ' in place of . to denote missing values) so as to avoid type conversion messages in the log and values such as '       .' instead of blank values.

Astounding
PROC Star

Are your variables character or numeric?  (It's not going to be that difficult, but it's easier to do once than twice.)

einstein
Quartz | Level 8

Variables are character.

Ksharp
Super User
data have;
infile cards truncover;
input Patient_ID $     Proc1    Proc2    Proc3    Proc4;
cards;
12345 42.1 56.2 22.1 33.4
23456 42.1 56.2 22.1 33.4
34567 .      .  22.1 33.4
;
run;
data want;
 set have;
 array p{*} proc1-proc4;
 array x{*} new1-new4;
 n=0;
 do i=1 to dim(p);
  if not missing(p{i}) then do;
   n+1;x{n}=p{i};
  end;
 end;
 do i=1 to dim(p);
  p{i}=x{i};
 end;
 drop i n new1-new4;
run;

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
  • 8 replies
  • 2698 views
  • 0 likes
  • 5 in conversation