using %scan (maybe) to extract date from macro variable

[brucehughw]

Hello,

I plan to use file names as macro arguments. The file names start with different numbers of characters and end with mmddyyyy.csv . To extract the mm (month), dd (day of month), and yyyy (year), I would like to use the %scan function (as opposed to the more complicated code needed to run prx functions in macros).

So if

%let myStr  = fileNameXy_blah_blah07282015.csv,

can someone please tell me if I can use %scan to get 07282015 from &myStr?

Thanks, Bruce

[LinusH]

Perhaps substr() with length() is an alternative?

[brucehughw]

Hmm,

so I would use %length to determine how many characters &myStr has and then figure out how far from the end I'd need to start as my first position in %substr. I'll give it a try. Thanks for the suggestion.

Bruce

[Reeza]

Will it always be CSV?

Keep in mind the reverse() function. A double reverse and substr would work if you always know that you'll have that structure.

untested:

reverse(substr(reverse(string), 4, 8));

[brucehughw]

Good idea, but I don't think %reverse is a macro function.

thanks, Bruce

[sbb]

Explore using macro language %SYSFUNC(...) along with SCAN (the function), along with modifier / string arguments to extract the numeric data desired.  The DOC is pretty self-explanatory as far as the various coding techniques and results / behavior desired.

Scott Barry
SBBWorks, Inc.

[slchen · Accepted solution]

%let myStr  = fileNameXy_blah_blah07282015.csv;

%let num=%sysfunc(compress(&mystr,,kd));

%put #

[ballardw]

With your exact example this works

%let date= %scan(&mystr,1,,AP);

%put &date;

[brucehughw]

Thanks very much. I wish I could check more than one "correct answer". Yours works fine.

Thanks again, Bruce

[Ron_MacroMaven]

string juggling, just like spinning plates

%let myStr

=fileNameXy_blah_blah07282015.csv;

*1234567890123456789012345678901234567890;

*                    ^------^;

%let length_date = %length(mmddccyy);

%put &=length_date;

%let position_dot =%index(&mystr,.);

%put &=position_dot;

%let number =

%substr(&mystr,%eval(&position_dot - &length_date),&length_date);

*eval not necessary in previous line;

%put &=number;

%let date = %sysfunc(mdy

(%substr(&number,1,2)

,%substr(&number,3,2)

,%substr(&number,5,4)

));

%put &=date;*is.a SAS date;

%put %sysfunc(putn(&date  ,weekdate29.));

see also the page and paper Macro Loops with Dates

http://www.sascommunity.org/wiki/Macro_Loops_with_Dates

hth