Help using Base SAS procedures

yesterday's date Char format

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

yesterday's date Char format

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.


Accepted Solutions
Solution
‎11-15-2013 02:07 PM
Respected Advisor
Posts: 3,156

Re: yesterday's date Char format

Posted in reply to ewhulbert

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;

View solution in original post


All Replies
Super User
Posts: 19,770

Re: yesterday's date Char format

Posted in reply to ewhulbert

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.

Occasional Contributor
Posts: 8

Re: yesterday's date Char format

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

Super User
Posts: 11,343

Re: yesterday's date Char format

Posted in reply to ewhulbert

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

Occasional Contributor
Posts: 8

Re: yesterday's date Char format

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?

Solution
‎11-15-2013 02:07 PM
Respected Advisor
Posts: 3,156

Re: yesterday's date Char format

Posted in reply to ewhulbert

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;

Occasional Contributor
Posts: 8

Re: yesterday's date Char format

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.

Respected Advisor
Posts: 3,156

Re: yesterday's date Char format

Posted in reply to ewhulbert

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 929 views
  • 3 likes
  • 4 in conversation