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.
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;
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.
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'.
Try using a %sysfunc(quote(%sysfunc(PUTN(%eval(%sysfunc(date())-45),yymmdd10.)));
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?
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;
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.
LOL. Thanks for the offer. Haven't been there for more than 5 years. Nice town.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.