Hi All,
I have a date at the beginning of my code. %let crundate=161028;
I need to use the above to return '28Oct2016' as a date
and "28 October 2016" as a string
and "oct2016" as a string
and if possible the 1st and last day of the month (in this case October)
I use this %let ssrundate=%substr(&crundate,1,4); to return '1610'. But I cant seem to get any other formats I want.
The reason I need this, is that there a numerous sas files dropped into my folders and all have different naming conventions.
Examples: FileAoct2016, FileB28Oct2016, FileC1610 (FileA, FileB ect are not the actual names, but the dates at the end are actual values).
In addition I would like to use the 161028 to returns date values as mentioned above
%StartofMonth = &crundate ( this would return 01Oct2016) atm I'm coding this as %let StartofMonth= "01Oct2016"d;
%EndofMonth = &crundate ( this would return 31Oct2016) atm I'm coding this as %let StartofMonth= "31Oct2016"d;
%Drundate= &crundate ( this would return oct16) atm I'm coding this as %let Drundate= oct16;
SAS does it all very nicely for you, with just a couple of additions, assuming I've understood you correctly.
For a start, it makes things much easier if you store your date value as "days since 1 Jan 1960", or 20755 in this case. The easiest way to do this is to use the %sysevalf function:
%let rundate = %sysevalf("28oct2016"d); /* which requires you to reformat your date, of course */
Alternatively:
%let rundate = %sysfunc(inputn(161028, yymmdd6.));
Now, the intnx function is your friend. This will return the beginning, middle or end of a period ('month' in this case, obviously). Couple that with some formats and you're away.
%let startofmonth = %sysfunc(intnx(month, &rundate, 0, b), date9.); %let endofmonth = %sysfunc(intnx(month, &rundate, 0, e), date9.); %let drundate = %sysfunc(putn(&rundate, monyy5.)); %put &startofmonth &endofmonth &drundate;
This displays:
01OCT2016 31OCT2016 OCT16
Note that this will return 1OCT2016 etc. To convert that in macro code is a little ugly, but will be reliable:
%let endofmonth = %substr(&endofmonth, 1, 3)%lowcase(%substr(&endofmonth, 4, 2))%substr(&endofmonth, 6, 4);
If you want to see 1 October 2016 instead (which doesn't require the substringing):
%let startofmonth = %sysfunc(intnx(month, &rundate, 0, b), worddatx.);
Your other option, to return the year and month:
%put %sysfunc(putn(&rundate, yymmdd4.));
1610
SAS does it all very nicely for you, with just a couple of additions, assuming I've understood you correctly.
For a start, it makes things much easier if you store your date value as "days since 1 Jan 1960", or 20755 in this case. The easiest way to do this is to use the %sysevalf function:
%let rundate = %sysevalf("28oct2016"d); /* which requires you to reformat your date, of course */
Alternatively:
%let rundate = %sysfunc(inputn(161028, yymmdd6.));
Now, the intnx function is your friend. This will return the beginning, middle or end of a period ('month' in this case, obviously). Couple that with some formats and you're away.
%let startofmonth = %sysfunc(intnx(month, &rundate, 0, b), date9.); %let endofmonth = %sysfunc(intnx(month, &rundate, 0, e), date9.); %let drundate = %sysfunc(putn(&rundate, monyy5.)); %put &startofmonth &endofmonth &drundate;
This displays:
01OCT2016 31OCT2016 OCT16
Note that this will return 1OCT2016 etc. To convert that in macro code is a little ugly, but will be reliable:
%let endofmonth = %substr(&endofmonth, 1, 3)%lowcase(%substr(&endofmonth, 4, 2))%substr(&endofmonth, 6, 4);
If you want to see 1 October 2016 instead (which doesn't require the substringing):
%let startofmonth = %sysfunc(intnx(month, &rundate, 0, b), worddatx.);
Your other option, to return the year and month:
%put %sysfunc(putn(&rundate, yymmdd4.));
1610
Oh, I see. If you start off by converting your input string into a SAS date value, you have all the flexibility you need. So, starting off with:
%let crundate = 161028;
followed by:
%let rundate = %sysfunc(inputn(&crundate, yymmdd6.));
That will leave crundate effectively as a text string (although macro variables are untyped) and can be used as such, and rundate with a value of 20755. What I meant with the alternatives was that it was two ways of doing exactly the same thing. In retrospect, the second one seems closer to what you want.
I hope I've made things a little clearer…
Excellent. Glad to help. %sysfunc is really your friend.
Hi again,
The code that Laurie gave me works in that it returns date values (thanks again Laurie). However, using the below
%let crundate= 161028;
%let irsbrundate = %sysfunc(inputn(&crundate, yymmdd6.)); %let StartofMonth = %sysfunc(intnx(month, &irsbrundate, 0, b), date9.); %let EndofMonth = %sysfunc(intnx(month, &irsbrundate, 0, e), date9.);
with
data reports.PartiFail&crundate; set reports.PartiFail&crundate; retain Start_of_Month&StartofMonth ; format Start_of_Month date9.; retain End_of_Month&EndofMonth; format End_of_Month date9.; run;
Does not return values for Start_of_Month nor End_of_Month. "." are returned on every row.
However,
%let StartMonth= "01Oct2016"d; %let EndMonth = "31Oct2016"d;
and
data reports.PartiFail&crundate; set reports.PartiFail&crundate; retain Start_of_Month&StartMonth ; format Start_of_Month date9.; retain End_of_Month&EndMonth; format End_of_Month date9.; run;
does return date values (01OCT2016 and 31OCT2016).
Looking at SAS Macro Variable Viewer is SAS EG, StartMonth returns "01Oct2016"d and StartofMonth returns 01OCT2016.
Is there something else I need to do, so I dont have to hard code the first day of the month and last day of the month, based off crundate?
Ah - I think you've got the external and internal values of the variables mixed up.
The first block of code has them stored as the external representations. If you convert it to:
%let crundate= 161028;
%let irsbrundate = %sysfunc(inputn(&crundate, yymmdd6.));
%let StartofMonth = %sysfunc(intnx(month, &irsbrundate, 0, b));
%let EndofMonth = %sysfunc(intnx(month, &irsbrundate, 0, e));
with
data reports.PartiFail&crundate;
set reports.PartiFail&crundate;
retain Start_of_Month &StartofMonth ;
format Start_of_Month date9.;
retain End_of_Month &EndofMonth;
format End_of_Month date9.;
run;
Note that the intnx functions within the %sysfuncs no longer have the format applied to them, so the %sysfuncs only return the internal values.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.