topic Re: Update all records corresponding to the same ID with the value of a variable in New SAS User

Update all records corresponding to the same ID with the value of a variable

NewUsrStat — Thu, 30 May 2024 13:53:56 GMT

Hi guys,

suppose to have the following:

data DB;
input ID :$20. Discharge :date9. Discharge_n :date9.;
format Discharge :date9. Discharge_n :date9.;
cards;
0001 13FEB2019 .
0001 01FEB2019 28FEB2019
0002 01FEB2020 .
0002 04FEB2020 28FEB2020
;

then you want to update Discharge (all the fields, missings and not) with the variable Discharge_n to get:

data DB1;
  input ID :$20. Discharge :date9. Discharge_n :date9.;
  format Discharge date9. Discharge_n :date9.;
cards;
0001 28FEB2019     .      
0001 28FEB2019  28FEB2019
0002 28FEB2020     .
0002 28FEB2020  28FEB2020
;

If I use the rule: if discharge_n > discharge only the corresponding row will be updated but I would like to update all rows corresponding to the same ID.

Can anyone help me please?

Thank you in advance

Re: Update all records corresponding to the same ID with the value of a variable

ballardw — Thu, 30 May 2024 14:43:09 GMT

One way:

data dd;
input ID :$20. DD :date9.   ;
datalines;
0001 28FEB2019
0002 28FEB2020
;

data want;
   merge db dd (In=InDD);
   by id;
   if indd then do;
      if not missing (discharge) then discharge=dd;
      if not missing (discharge_n) then discharge_n=dd;
   end;
   drop dd;
run;

The question is where the knowledge of which ID and which date to update the values with may come from as that was not stated anywhere that I could see.

I provided a different ID to show that a subset of ID's can be addressed this way.

Re: Update all records corresponding to the same ID with the value of a variable

Ksharp — Fri, 31 May 2024 03:28:53 GMT

data DB;
input ID :$20. Discharge :date9. Discharge_n :date9.;
format Discharge :date9. Discharge_n :date9.;
cards;
0001 13FEB2019 .
0001 01FEB2019 28FEB2019
0002 01FEB2020 .
0002 04FEB2020 28FEB2020
;
data want;
do until(last.id);
 set DB;
 by id;
 if last.id and discharge_n > discharge then do;flag=1;temp=Discharge_n;end;
end;
do until(last.id);
 set DB;
 by id;
 if flag then do;
   if not missing(Discharge) then Discharge=temp;
   if not missing(Discharge_n) then Discharge_n=temp;
   output;
 end;
end;
drop flag temp;
run;