topic Re: using %scan (maybe) to extract date from macro variable in SAS Programming

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

brucehughw — Tue, 28 Jul 2015 18:11:17 GMT

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

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

LinusH — Tue, 28 Jul 2015 18:22:12 GMT

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

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

brucehughw — Tue, 28 Jul 2015 18:45:48 GMT

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

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

sbb — Tue, 28 Jul 2015 19:04:18 GMT

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.

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

slchen — Tue, 28 Jul 2015 19:04:23 GMT

%let myStr = fileNameXy_blah_blah07282015.csv;

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

%put &num;

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

Reeza — Tue, 28 Jul 2015 19:06:00 GMT

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));

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

ballardw — Tue, 28 Jul 2015 19:33:09 GMT

With your exact example this works

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

%put &date;

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

brucehughw — Tue, 28 Jul 2015 20:27:15 GMT

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

thanks, Bruce

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

brucehughw — Tue, 28 Jul 2015 20:29:02 GMT

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

Thanks again, Bruce

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

Ron_MacroMaven — Tue, 28 Jul 2015 23:35:26 GMT

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