yesterday's date Char format

[ewhulbert]

I am trying to write a program that pulls data from a SQL server for the last 45 days.  The format on the SQL server for the date is a datetime format.

I have this so far:

%let BEG = '2013-10-01';

%put &BEG;

%let END = '2013-11-14';

%put &END;

proc sql;

....where date between &BEG and &END...;

The above code works, but I would love to have something that automatically fills in yesterday's date and 45 days prior.  I tried this :

%let BEG = %sysfunc(PUTN(%eval(%sysfunc(date())-45),yymmdd10.));

%put &BEG;

%let END = %sysfunc(PUTN(%eval(%sysfunc(date())-1),yymmdd10.));

%put &END;

This almost works.  It outputs 2013-10-01 and 2013-11-14, but I need it to output '2013-10-01' and '2013-11-14' so that my where statement evaluates correctly.  Is that possible? 

Thanks for all of your help.

[Reeza]

Why not leave it as a number rather than try and format it?

%let BEG =%eval(%sysfunc(date())-45);

%let END = %eval(%sysfunc(date())-1);

The query will convert it appropriately, though it won't display as a date (2013-10-01).

But if your date in SQL server is datetime you may a different type of calculation depending on how you're extracting your data.

[ewhulbert]

The SQL server that I am pulling it from has the information formatted as date time, but there is no time entered.  It looks like this 2013-11-14 00:00:00.000.  Selecting it by using '2013-11-14' works, but using SAS dates like 19362 does not.  I am trying to get from the system yesterday's date and 45 days ago's date, in yymmdd10. format, but with quotes around it like '2013-11-14'.

[ballardw]

Try using a %sysfunc(quote(%sysfunc(PUTN(%eval(%sysfunc(date())-45),yymmdd10.)));

[ewhulbert]

So Close!  I thought this would work, but this puts out double quotes e.g. "2013-11-14" instead of single '2013-11-14', and apparently it does not like the doube quotes.

Great idea, do you know of a way to do it with single quotes?

[Haikuo · Accepted solution]

I can't think of an easy way to single quote it except catenating them in (or translate after the double quote):

data _null_;

  call symputx('beg', cats("'",put(date()-45,yymmdd10.),"'"));

run;

%put &beg;

[ewhulbert]

Thanks, you helped me again!  Your solution worked perfectly.  Let me know if you are ever in Jax, I owe you a couple beers.

On a side note, I am not sure why this doesnt work:

BEG = %sysfunc(PUTN(%eval(%sysfunc(date())-45), yymmdd10.));

%unquote(%str(%')&BEG%str(%'));

END = %sysfunc(PUTN(%eval(%sysfunc(date())-1), yymmdd10.));

%unquote(%str(%')&END%str(%'));

It outputs what I want, but the Proc SQL is unable to get the information with it.  Oh well, i think the Hai.kuo's cats solution is more straightforward anyways, and I will use it in the future.

[Haikuo]

LOL. Thanks for the offer. Haven't been there for more than 5 years. Nice town.