SAS Date Conversion to Save on Database

[dhavalyparikh]

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;

[PaigeMiller]

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;

[dhavalyparikh]

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.

[PaigeMiller · Accepted solution]

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. 

[dhavalyparikh]

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;

[dhavalyparikh]

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?

[PaigeMiller]

---

@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?

[dhavalyparikh]

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.

[Kurt_Bremser]

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;

[dhavalyparikh]

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

[dhavalyparikh]

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

[Kurt_Bremser]

---

@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

[dhavalyparikh]

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.. ?