topic Re: Create new date variable based on specific condition in New SAS User

Create new date variable based on specific condition

tan-wongv — Sun, 28 Apr 2024 18:16:01 GMT

I have a data set contains DMRN, last visit date, the presence of UI, and recorded date of UI.

DMRN	last_visit	UI	recorded_time
31	26AUG2021	0	06APR2018
31	26AUG2021	0	16JAN2020
31	26AUG2021	1	4MAY2021
33	24MAY2022	0	02MAR2020
33	24MAY2022	0	24MAY2022
35	01DEC2014	0	25MAR2013

I would like to create new_date for each DMRN based on these conditions:
if UI = 1 then new_date = recorded_time
else if UI = 0 then new_date = last_visit
The outcome would be like as follow:

DMRN	last_visit	UI	recorded_time	new_date
31	26AUG2021	0	06APR2018	4MAY2021
31	26AUG2021	0	16JAN2020	4MAY2021
31	26AUG2021	1	4MAY2021	4MAY2021
33	24MAY2022	0	02MAR2020	24MAY2022
33	24MAY2022	0	24MAY2022	24MAY2022
35	01DEC2014	0	25MAR2013	01DEC2014

Could you please help me with the code? Thank you very much

Re: Create new date variable based on specific condition

Tom — Sun, 28 Apr 2024 19:26:38 GMT

You seem to have already written the code.

Did you try running it? What happened?

data want;
  set have;
  if UI = 1 then new_date = recorded_time;
  else if UI = 0 then new_date = last_visit;
  format new_date date9.;
run;

Re: Create new date variable based on specific condition

tan-wongv — Mon, 29 Apr 2024 00:04:08 GMT

This code works for those who have UI = 0, new_date is equal to last_visit

However, it did not work for those who have UI = 1, such as DMRN 31. I would like new_date for DMRN31 to be as 4MAY2021 4MAY2021 4MAY2021

DMRN	last_visit	UI	recorded_time	new_date
31	26AUG2021	0	06APR2018	26AUG2021
31	26AUG2021	0	16JAN2020	26AUG2021
31	26AUG2021	1	4MAY2021	4MAY2021
33	24MAY2022	0	02MAR2020	24MAY2022
33	24MAY2022	0	24MAY2022	24MAY2022
35	01DEC2014	0	25MAR2013	01DEC2014

Re: Create new date variable based on specific condition

Tom — Mon, 29 Apr 2024 01:02:03 GMT

Works fine.

Are you sure you want the extra IF in there? What value do you want for NEW_VAR when UI is neither 1 nor zero?

data have;
  input DMRN $ last_visit :date. UI recorded_time :date.;
  format last_visit recorded_time date9.;
cards;
31 26AUG2021 0 06APR2018
31 26AUG2021 0 16JAN2020
31 26AUG2021 1 4MAY2021
31 26AUG2021 1 26MAY2022
33 24MAY2022 0 02MAR2020
33 24MAY2022 0 24MAY2022
35 01DEC2014 0 25MAR2013
35 01DEC2014 1 05JAN2015
;

data want ;
  set have;
  if ui=1 then new_var = recorded_time;
  else new_var = last_visit;
  format new_var date9.;
run;

Result

                   last_          recorded_
Obs    DMRN        visit    UI      time         new_var

 1      31     26AUG2021     0    06APR2018    26AUG2021
 2      31     26AUG2021     0    16JAN2020    26AUG2021
 3      31     26AUG2021     1    04MAY2021    04MAY2021
 4      31     26AUG2021     1    26MAY2022    26MAY2022
 5      33     24MAY2022     0    02MAR2020    24MAY2022
 6      33     24MAY2022     0    24MAY2022    24MAY2022
 7      35     01DEC2014     0    25MAR2013    01DEC2014
 8      35     01DEC2014     1    05JAN2015    05JAN2015

Again check that the values of UI are actually integers.

Re: Create new date variable based on specific condition

Patrick — Mon, 29 Apr 2024 01:51:30 GMT

If @Tom 's code doesn't return the desired result then run below and investigate any row that gets written to table Investigate.

data investigate ;
  set have;
  if ui not in (0,1) then output;
run;

or here another way to check what values you've actually got in UI.

proc freq data=have;
  table ui;
run;
proc contents data=have(keep=ui);
run;quit;

Re: Create new date variable based on specific condition

andreas_lds — Mon, 29 Apr 2024 06:22:52 GMT

Is it guaranteed that each dmrn has 0 or 1 obs with ui = 1, or could there be more obs with ui = 1?

data ui;
   set have(
         keep= DMRN recorded_time ui 
         rename=(recorded_time = new_date)
         where=(ui = 1)
      );

   drop ui;
run;


data want;
   set have;

   length new_date 8;
   format new_date date9.;

   if _n_ = 1 then do;
      declare hash h(dataset: 'ui');
      h.defineKey('dmrn');
      h.defineData('new_date');
      h.defineDone();
   end;

   if h.find() ^= 0 then do;
      new_date = recorded_time;
   end;
run;

Re: Create new date variable based on specific condition

Sajid01 — Sat, 04 May 2024 14:27:25 GMT

Hello @tan-wongv
If you want the outcome as you have shown, then you would need to modify @Tom 's code as follow

data have;
  input DMRN $ last_visit :date. UI recorded_time :date.;
  format last_visit recorded_time date9.;
cards;
31 26AUG2021 0 06APR2018
31 26AUG2021 0 16JAN2020
31 26AUG2021 1 4MAY2021
31 26AUG2021 1 26MAY2022
33 24MAY2022 0 02MAR2020
33 24MAY2022 0 24MAY2022
35 01DEC2014 0 25MAR2013
35 01DEC2014 1 05JAN2015
;

data want ;
  set have;
  if ui=1 then new_date = recorded_time;
  else if ui=0 and DMRN=31 then new_date="04MAY2021"d;
  else new_date = last_visit;
  format new_date date9.;
run;

The result would be as you wished