topic Re: SAS Date Conversion to Save on Database in Programming 1 and 2

SAS Date Conversion to Save on Database

dhavalyparikh — Wed, 31 Aug 2022 17:00:57 GMT

Data _null_;

MyDate = Today();

format MyDate mmddyy10.;

put "Current Date " MyDate;

next_rundate = intnx('week', MyDate, 1,'B');

format next_rundate mmddyy10.

put "next Run Date " next_rundate; /* A - This print correct date after one week in mm/dd/yyyy format*/

run;

/* I want to store value at above line in to below update statement and in database field is date time current below command just store dot (.) in date field - what do I do to convert next_rundate in correct format so I can update thru table */

proc sql;

update lib.table1

set next_rundate = "&next_rundate" DT

where id =&id.;

Run;

Re: SAS Date Conversion to Save on Database

PaigeMiller — Wed, 31 Aug 2022 17:05:49 GMT

You should not need a macro variable or even two steps to do this. Here is how I would do this, although I haven't tested this at all. It may depend on how next_rundate is formatted in lib.table1.

proc sql;
    update lib.table1 set next_rundate = intnx('week',today(),1,'b')
    where id =&id.;
run;

Re: SAS Date Conversion to Save on Database

dhavalyparikh — Wed, 31 Aug 2022 17:33:13 GMT

Thanks Miller for you comment

This gives me date in 01JAN1960:06:21:32 format but not one week after.

also I would need to store that in macro variable because down the road I have some condition to populate

Week after

Month after

Quater after

Year after and save to database so I will have one update statement with value changing for my multiple if conditions.

Re: SAS Date Conversion to Save on Database

PaigeMiller — Wed, 31 Aug 2022 17:36:51 GMT

This should fix it

intnx('dtweek',datetime(),1,'b')

There's no need for a macro variable if the value is stored in a SAS data set, and you just want to find other dates from it.

Re: SAS Date Conversion to Save on Database

dhavalyparikh — Wed, 31 Aug 2022 17:40:03 GMT

That's kind of weird. Now it's giving me 03Jan1960:00:00:00

Is there way I can save this value in variable
and use this along with proc sql update posted on my first question.

proc sql;
update lib.table1
set next_rundate = "&next_rundate" DT
where id =&id.;
Run;

Re: SAS Date Conversion to Save on Database

dhavalyparikh — Wed, 31 Aug 2022 17:43:11 GMT

Sorry my bad the command which you sent below that works

intnx('dtweek',datetime(),1,'b')
but now Is there a way I can save this value in a variable and assign it with my update statement?

Re: SAS Date Conversion to Save on Database

PaigeMiller — Wed, 31 Aug 2022 17:45:45 GMT

@dhavalyparikh wrote:
Sorry my bad the command which you sent below that works

intnx('dtweek',datetime(),1,'b')
but now Is there a way I can save this value in a variable and assign it with my update statement?

Explain why you need to save it somewhere, other than in lib.table1 where it is already saved?

Re: SAS Date Conversion to Save on Database

Kurt_Bremser — Wed, 31 Aug 2022 17:48:06 GMT

@dhavalyparikh wrote:
That's kind of weird. Now it's giving me 03Jan1960:00:00:00

Can't be.

data _null_;
next_week = intnx('dtweek',datetime(),1,'b');
format next_week e8601dt19.;
put "next week= " next_week;
run;

Log:

 68         
 69         data _null_;
 70         next_week = intnx('dtweek',datetime(),1,'b');
 71         format next_week e8601dt19.;
 72         put "next week= " next_week;
 73         run;
 
 next week= 2022-09-04T00:00:00

Re: SAS Date Conversion to Save on Database

dhavalyparikh — Wed, 31 Aug 2022 17:50:07 GMT

Thanks This works but as I want to have this value to keep on changing the value of dates in one variable and I will have one simple update statement instead of using the case - use if condition first to decide based on type.
if jtype ='Weekly'
nextdate = intnx('dtweek',datetime(),1,'b');
end;
else if jtype ='yearly'
nextdate = intnx('year',datetime(),1,'b');
end;

proc sql;
update lib.table1 set next_rundate = "&nextdate."DT
where id =&id.;
run;

something like above.. ?

Re: SAS Date Conversion to Save on Database

dhavalyparikh — Wed, 31 Aug 2022 17:53:53 GMT

the table has one date filed which stores the value of the next_rundate of the job.
and job can be yearly / monthly/ quarterly / six-monthly - so what I want to do is as and when my job completes it will stamp next_rundate in my table -so we will have a future upcoming job or if next run date not updates during the second run then we know that this job is still not executed.

Re: SAS Date Conversion to Save on Database

Kurt_Bremser — Wed, 31 Aug 2022 18:02:39 GMT

Use a CASE for the value:

proc sql;
update lib.table1
set next_rundate = (case
  when jtype = "week" then intnx('dtweek',datetime(),1,'b')
  when jtype = "year" then intnx('dtyear',datetime(),1,'b')
  else .
end)
where id =&id.;
run;

Re: SAS Date Conversion to Save on Database

dhavalyparikh — Wed, 31 Aug 2022 18:04:13 GMT

Thanks, Kurt, really appreciate your help - I will try and get back to you in a bit. Thanks again.

Re: SAS Date Conversion to Save on Database

dhavalyparikh — Wed, 31 Aug 2022 18:25:20 GMT

Yes, That works! Awesome! Really appreciate your help.