DATA Step, Macro, Functions and more

Convert YYMMDD to other formats

Accepted Solution Solved
Reply
Contributor
Posts: 37
Accepted Solution

Convert YYMMDD to other formats

[ Edited ]

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;


Accepted Solutions
Solution
‎10-30-2016 11:33 PM
Super Contributor
Posts: 251

Re: Convert YYMMDD to other formats

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

 

View solution in original post


All Replies
Solution
‎10-30-2016 11:33 PM
Super Contributor
Posts: 251

Re: Convert YYMMDD to other formats

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

 

Contributor
Posts: 37

Re: Convert YYMMDD to other formats

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?
Super Contributor
Posts: 251

Re: Convert YYMMDD to other formats

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…

 

Contributor
Posts: 37

Re: Convert YYMMDD to other formats

Thanks Laurie, Your solution worked a treat! :-)
Super Contributor
Posts: 251

Re: Convert YYMMDD to other formats

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

Contributor
Posts: 37

Re: Convert YYMMDD to other formats

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?

 

 

Super Contributor
Posts: 251

Re: Convert YYMMDD to other formats

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.

☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 373 views
  • 3 likes
  • 2 in conversation