topic Add new column with updates reflected without affecting missing data in SAS Data Management

Add new column with updates reflected without affecting missing data

AshJuri — Wed, 18 Mar 2020 21:22:12 GMT

Hi everyone, below is my working program that I would like to update.

data have;input Subject Type $ 5-12 Procedure $ 15-22 Measurement;datalines;
500 Initial   Invasive  20 
500 Initial   Surface   35 
500 Followup  Invasive  54 
428 Followup  Outer     29 
765 Seventh   Other     13 
500 Followup  Surface   98 
428 Initial   Outer     10 
765 Initial   Other     19 
610 Third     Invasive  66 
610 Initial   Invasive  17 
;
data want (drop=rc _Measurement);
   if _N_ = 1 then do;
   declare hash h (dataset : "have (rename=(Measurement=_Measurement) where=(Type='Initial'))");      h.definekey ('Subject');      h.definedata ('_Measurement');      h.definedone();
   end;

   set have;   _Measurement=.;

   if Type ne 'Initial' then rc = h.find();   Measurement = sum (Measurement, -_Measurement);run;


The goal is to subtract the numbers in the "MEASUREMENT" column based on the "SUBJECT", 
"TYPE" and “PROCEDURE” columns. If two values in the “SUBJECT” column match and two values 
in the “PROCEDURE” column match, then the initial measurement should be subtracted from the 
other measurement. For example, the initial measurement in row 1 (20) should be subtracted from 
the followup measurement in row 3 (54) because the subject (500) and procedure (Invasive) match.
Furthermore, the initial measurement in row 8 (19) should be subtracted from the seventh 
measurement in row 5 (13) because the subject (765) and procedure (Other) match. 
The result should form the "OUTPUT" column. 

How can I account for missing values in the "measurement" column and 
keep them without being affected in the "output" column?? 
In addition, I noticed that this program updates the "measurement" column. 
How can I have the updates be created in a new column called "output" while keeping the "measurement" column?

Re: Add new column with updates reflected without affecting missing data

PeterClemmensen — Wed, 18 Mar 2020 21:51:00 GMT

Here is a hashing solution. I created a single missing value in your data for demonstration.

data have;
input Subject Type $ 5-12 Procedure $ 15-22 Measurement;
datalines;
500 Initial   Invasive  20 
500 Initial   Surface   35 
500 Followup  Invasive  54 
428 Followup  Outer     29 
765 Seventh   Other     13 
500 Followup  Surface   .  
428 Initial   Outer     10 
765 Initial   Other     19 
610 Third     Invasive  66 
610 Initial   Invasive  17 
;

data want (drop=rc _Measurement);
   if _N_ = 1 then do;
      declare hash h (dataset : "have (rename=(Measurement=_Measurement) where=(Type='Initial'))");
      h.definekey ('Subject');
      h.definedata ('_Measurement');
      h.definedone();
   end;

   set have;
   _Measurement=.;

   if Type ne 'Initial' then rc = h.find();
   NewMeasurement = ifn(Measurement=., ., sum (Measurement, -_Measurement));
run;

Re: Add new column with updates reflected without affecting missing data

ballardw — Wed, 18 Mar 2020 22:42:40 GMT

Please only post code or log results in the code boxes. Long narratives can be very hard to read. The main message window will flow text better as that is the purpose. The code box is better for actual code and log.