topic Re: Looping for String Variable in SAS Programming

Looping for String Variable

DavidLie — Tue, 18 Apr 2017 04:43:50 GMT

Hi SAS Expert!

I have a question on looping for string variables. First, I have multiple datasets and each of the file starts with the name "TMIDRRPT_MONTHLY_" and then followed by month and year. For example:

TMIDRRPT_MONTHLY_January-2010.txt

TMIDRRPT_MONTHLY_February-2010.txt

... etc.

I have created a macro to import multiple files in one go but it kept giving me the error code. If you can suggest a better way and/or spot my mistake, that will be greatly appreciated.

Many thanks!

David

%let year=2010;

%macro imp(year);
%do i=2010 %to &year;
%do j='January','February','March';

proc import datafile="&input\TMIDRRPT_MONTHLY_&j-&i.txt" out=data.raw&j&i dbms=dlm replace;
   delimiter='|';
   getnames=yes;
run;

%end;
%end;
%mend;
%imp(&year);

Re: Looping for String Variable

Reeza — Tue, 18 Apr 2017 04:49:26 GMT

What's the error?

Post the log.

If you search here you'll find some macros that can help with this issue. If all your files have the same format you can use a wildcard and import all at once.

Re: Looping for String Variable

DavidLie — Tue, 18 Apr 2017 04:56:19 GMT

Hi Reeza,

Here is the error message.

508 %let year=2010;
509
510 %macro imp(year);
511 %do i=2010 %to &year;
512 %do j='January','February';
ERROR: Expected %TO not found in %DO statement.
ERROR: A dummy macro will be compiled.
513
514 proc import datafile="&input\TMIDRRPT_MONTHLY_&j-&i.txt" out=data.raw&j&i dbms=dlm
514! replace;
515     delimiter='|';
516     getnames=yes;
517 run;
518
519 %end;
520 %end;
521 %mend;
522 %imp(&year);
     -
     180
WARNING: Apparent invocation of macro IMP not resolved.

ERROR 180-322: Statement is not valid or it is used out of proper order.

Re: Looping for String Variable

Reeza — Tue, 18 Apr 2017 05:02:13 GMT

I think Example 3 and 11 are what you're looking for, 3 is the solution to your question here and 11 is another option for your loop.

https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Appendix/ta-p/291716

Note that you can use the MONNAME format to turn a date into the month name, ie January.

%put %sysfunc(putn('01Jan2017'd, monname.));

The log will show

January.

Re: Looping for String Variable

ArtC — Tue, 18 Apr 2017 05:27:20 GMT

As you go through the examples that @Reeza pointed out you will discover that not all DATA step knowledge is transferrable to the macro language. For instance as you already found, macro %DO loops cannot iterate across character strings with multiple loop specifications - as can DATA step loops.

Also when appending text to a macro variable the dot or period is used to identify the end of the macro variable name. when appending a dot, a double dot is needed. in part your file name becomes &i-&j..txt;

Re: Looping for String Variable

Astounding — Tue, 18 Apr 2017 09:52:58 GMT

Here's some detail about the original situation causing the problem:

http://blogs.sas.com/content/sastraining/2015/01/30/sas-authors-tip-getting-the-macro-language-to-perform-a-do-loop-over-a-list-of-values/

As Art mentioned, you will need to add the dot as well.