Help using Base SAS procedures

Making dummy values continue into subsequent observations

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

Making dummy values continue into subsequent observations

[ Edited ]

Hi,

 

I have a dataset with records of patients and their diagnoses. Here is a simplified version:

 

data patients ;
infile datalines dsd delimiter=' '; 
input patientID $ year $ diagA $ diagB $ diagC $ ;
datalines;
1 2010 . . .
1 2011 . 1 .
1 2012 . . 1
1 2014 . . .
2 2009 1 . .
2 2010 1 . .
2 2013 . 1 .
2 2015 . . .
;
run;

What code can I use to make sure that once a patient has received a diagnosis, a value of 1 is inserted under that variable for each subsequent observation? To illustrate, I want this:

 

 

 

data patients2 ;
infile datalines dsd delimiter=' '; 
input patientID $ year $ diagA $ diagB $ diagC $ ;
datalines;
1 2010 . . .
1 2011 . 1 .
1 2012 . 1 1
1 2014 . 1 1
2 2009 1 . .
2 2010 1 . .
2 2013 . 1 .
2 2015 1 1 .
;
run;

Accepted Solutions
Solution
‎04-12-2016 10:25 AM
Respected Advisor
Posts: 3,777

Re: Making dummy values continue into subsequent observations

Your want doesn't look right to me.

 

data patients; 
   input patientID $ year $ diagA $ diagB $ diagC $; 
   datalines; 
1 2010 . . . 
1 2011 . 1 . 
1 2012 . . 1 
1 2014 . . . 
2 2009 1 . . 
2 2010 1 . . 
2 2013 . 1 . 
2 2015 . . . 
;;;; 
   run;
data patients;
   update patients(obs=0) patients;
   by patientid;
   output;
   run;
proc print;
   run;

Capture.PNG

View solution in original post


All Replies
Super User
Super User
Posts: 7,401

Re: Making dummy values continue into subsequent observations

[ Edited ]

Bit verbose, am on a meeting so cant think properly:

data want (drop=lstdiag:);
set patients;
retain lstdiaga lstdiagb lstdiagc;
if diaga ne "" then lstdiaga=diaga;
if diagb ne "" then lstdiagb=diagb;
if diagc ne "" then lstdiagc=diagc;
diaga=coalescec(diaga,lstdiaga);
diagb=coalescec(diagb,lstdiagb);
diagc=coalescec(diagc,lstdiagc);
run;
Contributor
Posts: 28

Re: Making dummy values continue into subsequent observations

Thank you for your help, I can see the logic in your code. The problem is that I have more than 10 diagnosis variables, and I am reluctant to write one line of code for each one of them. Do you know if there's any way of working with arrays to get around this?
Super User
Posts: 6,935

Re: Making dummy values continue into subsequent observations


udden2903 wrote:
Thank you for your help, I can see the logic in your code. The problem is that I have more than 10 diagnosis variables, and I am reluctant to write one line of code for each one of them. Do you know if there's any way of working with arrays to get around this?

@data_null__'s code will take care of that quite nicely, as it lets SAS do it automatically.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,401

Re: Making dummy values continue into subsequent observations

Why do you have more than 10 diagnosis variables?  If you normalise your data you will find programming is far easier:

PatientId    Diag_No    Diag

1               1               xyz

1               2               zyf

 

You can do it with arrays of course, but it just makes programming more tricky:

%let elements=3;

data patients ; 
  infile datalines dsd delimiter=' '; 
  input patientID $ year $ diagA $ diagB $ diagC $ ; 
datalines; 
1 2010 . . . 
1 2011 . 1 . 
1 2012 . . 1 
1 2014 . . . 
2 2009 1 . . 
2 2010 1 . . 
2 2013 . 1 . 
2 2015 . . . 
; 
run;

data want (drop=i ret:);
  set patients;
  array ret{&elements.} $3;
  array act{&elements.} diag:;
  retain ret:;
  do i=1 to &elements.;
    if act{i} ne "" then ret{i}=act{i};
    act{i}=coalescec(act{i},ret{i});
  end;
run;  

 

Contributor
Posts: 28

Re: Making dummy values continue into subsequent observations

A patient can have many different combinations of diagnoses over time. Concatenating the diagnosis dummy variables into one single variable will not be helpful as I want to know, for each observation, all of the diagnoses the patient has had up until that point. I think I would miss that with your solution...
Super User
Super User
Posts: 7,401

Re: Making dummy values continue into subsequent observations

No, you can do any processing you can do with tranposed data with normalised data.  The only difference is you don't need to know how many observations up front as the structure does not change, only the amount of data to process.  With transposed data, the strcuture changes with more or fewer data elements, making programming more difficult.

 

Super User
Posts: 6,935

Re: Making dummy values continue into subsequent observations

A slight variation on @RW9's logic:

%macro checkvar(variable);
retain old&variable;
if not first.patientid and old&variable ne '' then &variable = old&variable;
old&variable = &variable;
drop old&variable;
%mend;

data patients2;
set patients;
by patientid;
%checkvar(diaga);
%checkvar(diagb);
%checkvar(diagc);
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 28

Re: Making dummy values continue into subsequent observations

Is there a way of using the retain statement with an array statement to solve my problem? I have more than 10 diagnoses in my original dataset and I would like to make the code as efficient as possible by invoking something like an array statement.
Solution
‎04-12-2016 10:25 AM
Respected Advisor
Posts: 3,777

Re: Making dummy values continue into subsequent observations

Your want doesn't look right to me.

 

data patients; 
   input patientID $ year $ diagA $ diagB $ diagC $; 
   datalines; 
1 2010 . . . 
1 2011 . 1 . 
1 2012 . . 1 
1 2014 . . . 
2 2009 1 . . 
2 2010 1 . . 
2 2013 . 1 . 
2 2015 . . . 
;;;; 
   run;
data patients;
   update patients(obs=0) patients;
   by patientid;
   output;
   run;
proc print;
   run;

Capture.PNG

Super User
Posts: 6,935

Re: Making dummy values continue into subsequent observations

Creative! Like it, @data_null__

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Respected Advisor
Posts: 3,777

Re: Making dummy values continue into subsequent observations

[ Edited ]

This version of the update method unsures that only variables of interest "DIAG:" are carried forward.

 

data patients; 
   input patientID $ year $ diagA $ diagB $ diagC $; 
   datalines; 
1 2010 . . . 
1 2011 . 1 . 
1 2012 . . 1 
1 2014 . . . 
2 2009 1 . . 
2 2010 1 . . 
2 2013 . 1 . 
2 2015 . . . 
;;;; 
   run;
%let locf=patientid diag:;
data patients;
   if 0 then set patients;
   update patients(obs=0 keep=&locf) patients(keep=&locf);
   by patientid;
   set patients(drop=&locf);
   output;
   run;
proc print;
   run;

 

Contributor
Posts: 28

Re: Making dummy values continue into subsequent observations

[ Edited ]

I can't get this code to work. In my actual dataset, the diagnoses have names like "L40" and "K50", so I would have to change this part of your code:

%let locf=patientid diag:;

I tested it for the L40 diagnosis, using (%let locf=patientid L:; ), but it still does not work.

Respected Advisor
Posts: 3,777

Re: Making dummy values continue into subsequent observations

[ Edited ]

You need to mention all relevant details when your post.  If your names are not DIAG: as you implied then obviously DIAG: will not work for you.

 

If you were to define an array of the diagnosis variables then how would you do that?

Would you use a name range list?  You can always list the individual names.  Or use some combination of "SAS Variable Lists" and names etc.

 

You need to learn about the "SAS Variable List".  

 

 

Contributor
Posts: 28

Re: Making dummy values continue into subsequent observations

I implemented your code, replacing diag: with K: L: M:

 

You are right, I shouldn't have named my diagnosis variables DiagA, DiagB and DiagC. I was simply trying to make the problem more straightforward to the viewer, but I will avoid making such changes in the future.

 

Would you mind quickly explaining what your code does? I'm new to macros and I think gaining some understanding of your code would be helpful as I start learning more about them.

☑ This topic is SOLVED.

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

Discussion stats
  • 19 replies
  • 735 views
  • 3 likes
  • 5 in conversation