The SAS Output Delivery System and reporting techniques

Trying to loop macro

Reply
N/A
Posts: 0

Trying to loop macro

Hello, could someone help me in the right direction?

I am trying to use a macro that will:
1. obtain variables from a separate dataset
2. use these variable to create a unique name
3. use the unique name to name a new dataset
4. use the unique name to call a unique text file for import

My problem, is that this will work one time; however, there are 765 records in the dataset for which I am calling in the macro - why does this not go row by row and create 765 unique datasets?

Needless to say, I've spent considerable amount of time Google searching to figure this out by now. Thanks!!

Hope this makes sense. Here is a slimmed version of my code:

%MACRO IMPORTGSOD;

DATA _NULL_;
SET CLIMATE.STATIONSCOMBINED;
CALL SYMPUT ("USAFtemp", USAF);
CALL SYMPUT ("WBANtemp", WBAN);

%LET NAME = &USAFtemp._&WBANtemp.;
%LET USAFWBAN = &USAFtemp.-&WBANtemp.-1999.op;

%MEND IMPORTGSOD;


DATA WORK.STATION_1999_&NAME;
INFILE "C:\Users\Kevin\Documents\... ...\1999\&USAFWBAN"
FIRSTOBS = 2;


;
RUN;
N/A
Posts: 0

Re: Trying to loop macro

Posted in reply to deleted_user
That's right, it will only work one time. The data step in the macro block will read all the records in the source table and then store the appropriate value from the last record in the macro symbol table.

You then concatenate the values in your %Let, although I can't see why you need to do that, and those values are passed out of the macro block.

Then you create a table and read a file using the values from the last record of the stations table.

Your immediate problem is that you have split your four steps into two steps in the macro and two steps outside. There are umpteen ways to do this, but the way with fewest changes would be as follows:

Add a parameter to your macro call with a name like MRowNum.
Modify the SET statement to use the MRowNum value to select a row using the Obs = option on the table.
Take out your NAME and USAFWBAN symbols which only confuse the code, and use the temp values directly in the WORK.STATION... data step.
Move that step into the macro block.
Now test the code by calling the macro with %ImportGSOd( MRowNum = 1).
Then test again with another value of MRowNum.

If this all works, then you want to call the macro 765 times, and you could write the command lines above for that number of times. Or you can use the source table to build a control file in this manner.

FileName RUNMACRO Temp;

Data _NULL_;
Set CLIMATE.STATIONSCOMBINED( Obs = 3);
File RUNMACRO;
Put '%ImportGSOd( MRowNum = ' _N_ ');';
Run;

Verify the RUNMACRO file creates correctly by the following statement.

%Include RUNMACRO;

FileName RUNMACRO Clear;

If the first three tables were created then your syntax is good and you remove the Obs statement from the RUNMACRO data step and it will build a command file for all rows in the table and you can now face the issue of concatenating the tables or whatever it is you ned to do next.

Kind regards

David
N/A
Posts: 0

Re: Trying to loop macro

Posted in reply to deleted_user
Thanks so much for your tips. Let me ask a couple more questions. From your advice, I come up with:

%MACRO IMPORTGSOD(mROWNUM=);
DATA _NULL_;
SET CLIMATE.STATIONSCOMBINED(OBS = mROWNUM);
CALL SYMPUT ("USAFtemp", USAF);
CALL SYMPUT ("WBANtemp", WBAN);

DATA WORK.STATION_1999_&USAFtemp.&WBANtemp.;
INFILE "C:\Users\Kevin\Documents\......\1999\&USAFtemp.-&WBANtemp.-1999.op"
FIRSTOBS = 2;

INPUT
...........
;

INFORMAT
........
;
RUN;
%MEND IMPORTGSOD;

%IMPORTGSOD(mROWNUM = 1);

However, with this, I get the following errors:

1 DATA _NULL_; SET CLIMATE.STATIONSCOMBINED(OBS = mROWNUM); CALL SYMPUT ("USAFtemp", USAF)
-------
23
1 ! ; CALL SYMPUT ("WBANtemp", WBAN); DATA WORK.STATION_1999_&USAFtemp.&WBANtemp.; INFILE
ERROR: Invalid number conversion on MROWNUM.
WARNING: Apparent symbolic reference USAFTEMP not resolved.

ERROR 23-7: Invalid value for the OBS option.

NOTE: Numeric values have been converted to character values at the places given by:
(Line)Smiley SadColumn).
1:87 1:119
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds



NOTE: Line generated by the invoked macro "IMPORTGSOD".
1 DATA WORK.STATION_1999_&USAFtemp.&WBANtemp.; INFILE

-

22

---------

201
WARNING: Apparent symbolic reference WBANTEMP not resolved.
WARNING: Apparent symbolic reference USAFTEMP not resolved.
WARNING: Apparent symbolic reference WBANTEMP not resolved.

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, (, /, ;,
_DATA_, _LAST_, _NULL_.

ERROR 201-322: The option is not recognized and will be ignored.

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.STATION_1999_ may be incomplete. When this step was stopped there
were 0 observations and 30 variables.
WARNING: Data set WORK.STATION_1999_ was not replaced because this step was stopped.


------------------------------------------------------

It appears to me that there is an issue with:
1. passing the "1" for MNUMROW into the Macro, and
2. some problem with reading in the variables from USAF and WBAN into the macro.

I've tried putting the Data/Infile/Input/Informat stuff outside the macro, but this just changes up the error messages; however, it does work if I do this And change the Set parameter from (OBS = mROWNUM) to (OBS = 1); but I'm not sure why this isn't being passed into mROWNUM.

Any other suggestions?

I Greatly appreciate your help, this is pushing what I learned last semester in our SAS class, I don't know if we'll cover macros next semester, but I really need to figure out how to do something like this for a couple projects of mine.

THANKS!
N/A
Posts: 0

Re: Trying to loop macro

Posted in reply to deleted_user
I think given your knowledge level on macros that you should be talking to Tech Support if this doesn't get you working. We have strayed a long way off topic for this discussion group.

Your mistake is a simple one, you need to resolve the symbol in the set statement by preceding the parameter with an ampersand. Otherwise SAS will not recognise it is a macro symbol and will attempt to associate it with a variable. Once you do that, the macro symbols will populate correctly and the process should work.

You still need to have the two data steps within the macro since you will execute each of them once for each value of station etc.

Kind regards

David
N/A
Posts: 0

Re: Trying to loop macro

Posted in reply to deleted_user
Ah, I see. Thanks, got it to work and was able to import about 800 files per year for 10 years (almost 8,000!) in one fell swoop (added a macro to change the year for each iteration as well).

Thank you so much!
Ask a Question
Discussion stats
  • 4 replies
  • 140 views
  • 0 likes
  • 1 in conversation