topic Re: yesterday's date Char format in SAS Procedures

yesterday's date Char format

ewhulbert — Fri, 15 Nov 2013 17:29:47 GMT

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.

Re: yesterday's date Char format

Reeza — Fri, 15 Nov 2013 17:46:00 GMT

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.

Re: yesterday's date Char format

ewhulbert — Fri, 15 Nov 2013 18:21:38 GMT

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

Re: yesterday's date Char format

ballardw — Fri, 15 Nov 2013 18:46:05 GMT

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

Re: yesterday's date Char format

ewhulbert — Fri, 15 Nov 2013 18:53:28 GMT

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?

Re: yesterday's date Char format

Haikuo — Fri, 15 Nov 2013 19:07:19 GMT

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;

Re: yesterday's date Char format

ewhulbert — Fri, 15 Nov 2013 19:28:38 GMT

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.

Re: yesterday's date Char format

Haikuo — Fri, 15 Nov 2013 19:51:34 GMT

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