topic Re: Making dummy values continue into subsequent observations in SAS Procedures

Making dummy values continue into subsequent observations

udden2903 — Tue, 12 Apr 2016 11:51:26 GMT

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;

Re: Making dummy values continue into subsequent observations

RW9 — Tue, 12 Apr 2016 12:32:39 GMT

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;

Re: Making dummy values continue into subsequent observations

Kurt_Bremser — Tue, 12 Apr 2016 12:42:43 GMT

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;

Re: Making dummy values continue into subsequent observations

data_null__ — Tue, 12 Apr 2016 12:44:19 GMT

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;

Re: Making dummy values continue into subsequent observations

Kurt_Bremser — Tue, 12 Apr 2016 12:57:05 GMT

Creative! Like it, @data_null__

Re: Making dummy values continue into subsequent observations

udden2903 — Tue, 12 Apr 2016 13:05:53 GMT

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?

Re: Making dummy values continue into subsequent observations

Kurt_Bremser — Tue, 12 Apr 2016 13:14:59 GMT

@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.

Re: Making dummy values continue into subsequent observations

udden2903 — Tue, 12 Apr 2016 13:18:09 GMT

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.

Re: Making dummy values continue into subsequent observations

RW9 — Tue, 12 Apr 2016 13:20:32 GMT

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;

Re: Making dummy values continue into subsequent observations

udden2903 — Tue, 12 Apr 2016 13:27:02 GMT

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...

Re: Making dummy values continue into subsequent observations

data_null__ — Tue, 12 Apr 2016 13:38:47 GMT

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;

Re: Making dummy values continue into subsequent observations

RW9 — Tue, 12 Apr 2016 13:36:19 GMT

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.

Re: Making dummy values continue into subsequent observations

udden2903 — Tue, 12 Apr 2016 13:54:48 GMT

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.

Re: Making dummy values continue into subsequent observations

data_null__ — Tue, 12 Apr 2016 14:22:18 GMT

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".

Re: Making dummy values continue into subsequent observations

udden2903 — Tue, 12 Apr 2016 14:25:04 GMT

I got it to work now! Thanks for your help!

Re: Making dummy values continue into subsequent observations

Ksharp — Wed, 13 Apr 2016 02:55:17 GMT

Be careful. John King's code would work only if year don't have any missing value .

Re: Making dummy values continue into subsequent observations

data_null__ — Wed, 13 Apr 2016 11:36:05 GMT

@Ksharp wrote:

Be careful. John King's code would work only if year don't have any missing value .

@Ksharpapparently you didn't read the whole thread. You don't know what you're talking about.

Re: Making dummy values continue into subsequent observations

udden2903 — Thu, 14 Apr 2016 05:11:09 GMT

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.

Re: Making dummy values continue into subsequent observations

data_null__ — Thu, 14 Apr 2016 12:32:03 GMT

@udden2903 wrote:

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.

1 %let locf=patientid diag:;
2 data patients;
3   if 0 then set patients;
4   update patients(obs=0 keep=&locf) patients(keep=&locf);
5   by patientid;
6   set patients(drop=&locf);
7   output;
8   run;

define the list of variables and KEY(S) to be LOCFed so we only have to type it once.
create a new data set (same name as old)
preserve order of variables in the new data set using the old
update patients zeros obs with itself as transaction file (using missingcheck feature of update to LOCF)
update requires a by statement and this resets the LOCF at the start of each PATIENTID.
Bring in the variable we don't want LOCFed.
output needed because UPDATE outputs on LAST.(by-var) by default.
run

There is really no MACRO here just the macro variable (symbolic variable) LOCF

Re: Making dummy values continue into subsequent observations

udden2903 — Wed, 11 May 2016 07:11:59 GMT

How would you modify this code to make sure that the dummy values from the previous observation continue into the current observation ONLY if the current observation has missing values for ALL of the dummy variables? Right now, the value of a dummy variable is carried forward as long as the value of that variable is missing in the next observation, without any consideration of the values of the other dummy variables.