topic Re: update incorrect yesteraday'date column based on today's date in SAS Programming

update incorrect yesteraday'date column based on today's date

JJP1 — Fri, 13 Mar 2020 12:34:37 GMT

sample input code

data t;
input name $2. ydate :date9. tdate :date9.;
format ydate tdate date9.;
datalines;
jj 12MAR2020 13MAR2020
kk 15FEB2019 13MAR2020
rr 14FEB2020 15FEB2020
yy 21DEC2010 13MAR2019
;


run;

need to update only the ydate column with expression as (tdate-1)only if ydate is not equal to (tdate-1) and need sample output data as below.please suggest

jj 12MAR2020 13MAR2020
kk 12MAR2019 13MAR2020
rr 14FEB2020 15FEB2020
yy 12MAR2019 13MAR2019

Re: update incorrect yesteraday'date column based on today's date

PeterClemmensen — Fri, 13 Mar 2020 12:55:46 GMT

Why is the year 2019 in the second row?

Re: update incorrect yesteraday'date column based on today's date

Kurt_Bremser — Fri, 13 Mar 2020 13:08:30 GMT

"set to tdate -1 if it's not tdate - 1" is equivalent to "set to tdate - 1", period.

So you only have to execute

ydate = tdate - 1;

See this:

data t;
input name $2. ydate :date9. tdate :date9.;
format ydate tdate date9.;
datalines;
jj 12MAR2020 13MAR2020
kk 15FEB2019 13MAR2020
rr 14FEB2020 15FEB2020
yy 21DEC2010 13MAR2019
;

data want;
set t;
ydate = tdate - 1;
run;

proc print data=want noobs;
run;

Result:

name	ydate   	tdate
jj	12MAR2020	13MAR2020
kk	12MAR2020	13MAR2020
rr	14FEB2020	15FEB2020
yy	12MAR2019	13MAR2019

matches what you posted, with the exception of the 2019 typo.

Re: update incorrect yesteraday'date column based on today's date

JJP1 — Fri, 13 Mar 2020 13:14:11 GMT

thanks @Kurt_Bremser ,it is setting whole table but i need to update single column.i tried below option and it is working thanks.

proc sql;
update t
set ydate= tdate-1 where ydate<> tdate-1
;
quit;

Re: update incorrect yesteraday'date column based on today's date

Kurt_Bremser — Fri, 13 Mar 2020 13:44:39 GMT

@JJP1 wrote:

thanks @Kurt_Bremser ,it is setting whole table but i need to update single column.i tried below option and it is working thanks.
proc sql;
update t
set ydate= tdate-1 where ydate<> tdate-1
;
quit;

which is an inefficient solution.

See this:

data have;
do i = 1 to 1000000;
  tdate = rand('integer','01jan2000'd,today());
  if mod(i,3) = 0
  then ydate = tdate - 1;
  else ydate = rand('integer','01jan2000'd,today());
  output;
end;
format tdate ydate yymmddd10.;
run;

data want1;
set have;
ydate = tdate - 1;
run;

proc sql;
update have
set ydate = tdate - 1 where ydate <> tdate - 1
;
quit;

and this log from it:

73         data have;
 74         do i = 1 to 1000000;
 75           tdate = rand('integer','01jan2000'd,today());
 76           if mod(i,3) = 0
 77           then ydate = tdate - 1;
 78           else ydate = rand('integer','01jan2000'd,today());
 79           output;
 80         end;
 81         format tdate ydate yymmddd10.;
 82         run;
 
 NOTE: The data set WORK.HAVE has 1000000 observations and 3 variables.
 NOTE:  Verwendet wurde: DATA statement - (Gesamtverarbeitungszeit):
       real time           2.75 seconds
       cpu time            2.74 seconds
       
 
 83         
 84         data want;
 85         set have;
 86         ydate = tdate - 1;
 87         run;
 
 NOTE: There were 1000000 observations read from the data set WORK.HAVE.
 NOTE: The data set WORK.WANT1 has 1000000 observations and 3 variables.
 NOTE:  Verwendet wurde: DATA statement - (Gesamtverarbeitungszeit):
       real time           0.22 seconds
       cpu time            0.23 seconds
       
 
 88         
 89         proc sql;
 90         update have
 91         set ydate = tdate - 1 where ydate <> tdate - 1
 92         ;
 NOTE: Der Operator "<>" wird als "not equals" interpretiert.
 NOTE: 666568 rows were updated in WORK.HAVE.
 
 93         quit;
 NOTE:  Verwendet wurde: PROZEDUR SQL - (Gesamtverarbeitungszeit):
       real time           6.37 seconds
       cpu time            6.35 seconds

As you can see, the data step performs better by at least an order of magnitude. The result is the same, of course.