BookmarkSubscribeRSS Feed
tan-wongv
Obsidian | Level 7

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 

6 REPLIES 6
Tom
Super User Tom
Super User

You seem to have already written the code. 

Did you try running it?  What happened?

Spoiler
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;
tan-wongv
Obsidian | Level 7

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
Tom
Super User Tom
Super User

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.

 

 

Patrick
Opal | Level 21

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;
Sajid01
Meteorite | Level 14

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

Sajid01_0-1714832835085.png

 

andreas_lds
Jade | Level 19

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

 

Spoiler
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;

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 994 views
  • 0 likes
  • 5 in conversation