Convert YYMMDD to other formats

[Haydn]

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;

[LaurieF · Accepted solution]

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

[Haydn]

Thanks for you reply,

Sorry I should have mentioned that 161028 also still has to be returned as that is also appended to file names. Both %let rundate = %sysevalf("28oct2016"d); /* which requires you to reformat your date, of course */ and %let rundate = %sysfunc(inputn(161028, yymmdd6.)); return 20755. Was the above supposed to return that?

[LaurieF]

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…

[Haydn]

Thanks Laurie, Your solution worked a treat! 🙂

[LaurieF]

Excellent. Glad to help. %sysfunc is really your friend. 

[Haydn]

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?

[LaurieF]

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.