DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 37
Accepted Solution

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

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


Accepted Solutions
Solution
‎07-28-2015 03:04 PM
Super Contributor
Posts: 275

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

%let myStr  = fileNameXy_blah_blah07282015.csv;

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

%put #

View solution in original post


All Replies
Super User
Posts: 5,254

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

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

Data never sleeps
Contributor
Posts: 37

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

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

Super User
Posts: 17,750

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

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

Contributor
Posts: 37

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

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

thanks, Bruce

Super Contributor
Super Contributor
Posts: 3,174

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

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.

Solution
‎07-28-2015 03:04 PM
Super Contributor
Posts: 275

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

%let myStr  = fileNameXy_blah_blah07282015.csv;

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

%put #

Super User
Posts: 10,466

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

With your exact example this works

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

%put &date;

Contributor
Posts: 37

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

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

Thanks again, Bruce

Regular Contributor
Posts: 198

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

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

☑ This topic is SOLVED.

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

Discussion stats
  • 9 replies
  • 338 views
  • 3 likes
  • 7 in conversation