topic Re: Please help find why I cannot call this Macro m105p02.sas ! in SAS Programming

Please help find why I cannot call this Macro m105p02.sas !

hmlong25 — Fri, 14 Mar 2025 17:49:06 GMT

%Macro BasinXLS;

libname storm xlsx "&path/BasinStorms.xlsx";

%if SYSLIBRC ne 0 %then %do;

%put ERROR:Terminate Macro Execution;

%end;

%else %do;

Proc SQL noprint;

Select Distinct Basin, compress(BasinName)

into :Basin1-, :BasinName-

From mc1.storm_basin_codes;

quit;

%do i=1 %to &sqlobs;

Title "Group: &&Basin&i ";

data=storm.&&basinname&i.._Storms;

set mc1.storm_basin_codes;

where Basin=&&Basin&i;

run;

end;

%mend BasinXLS;

libname storm clear;

options mprint mlogic;

%BasinXLS

Re: Please help find why I cannot call this Macro m105p02.sas !

Kurt_Bremser — Fri, 14 Mar 2025 18:10:40 GMT

Please post the complete log by copy/pasting the text into a window opened with this button:

Note that this condition

%if SYSLIBRC ne 0  %then %do;

can never be true, as the text SYSLIBRC is obviously not equal to the text 0.

Re: Please help find why I cannot call this Macro m105p02.sas !

PaigeMiller — Fri, 14 Mar 2025 20:31:39 GMT

Taking a guess

data=storm.&&basinname&i.._Storms;

should be

data=storm.&&basinname&i..._Storms;

with three dots.

But really, don't make us guess, show us the log whenever you get errors, not just the errors in the log, we need to see the entire log for the macro. (And don't wait until we ask for the log either)

The underlying idea of your macro, to split a data set into smaller pieces, is something that we advise against in most cases. If you leave the data set as one big data set, you can access the parts that you want using a WHERE statement, or perform analyses using a BY statement. So, in most cases, do not split a large data set into many smaller data sets.

Re: Please help find why I cannot call this Macro m105p02.sas !

Astounding — Fri, 14 Mar 2025 19:13:35 GMT

A couple of good starting points ...

As others have pointed out, we don't know what the log says, so we are only guessing ...

This into: phrase looks incorrect:

into :Basin1-, :BasinName-

It should likely be:

into :Basin1-, :BasinName1-

And later when using the macro variables, are the basins actually numbers, or are they character strings? For numbers, your code is correct:

where Basin=&&Basin&i;

But for character strings you would need to add doublequotes:

where Basin="&&Basin&i";

Re: Please help find why I cannot call this Macro m105p02.sas !

Tom — Fri, 14 Mar 2025 21:50:52 GMT

Perhaps you re-typed some of this when making your post instead of copying the actual code? That might explain some of the obvious typos like missing & when referencing the SYSLIBRC macro variable and the missing numeric suffix on the target macro variable name in the INTO clause of the SQL query. And also perhaps why the libref defined to point at the XLSX file is not the one used later when querying the data?

The best path forward is to start over. Show working code you want to run that does not use any macros or reference any macro variables. Show it for at least 2 different basin values so we can see what part of the code the macro needs to modify.

Re: Please help find why I cannot call this Macro m105p02.sas !

hmlong25 — Sun, 16 Mar 2025 21:40:02 GMT

Thank you

Re: Please help find why I cannot call this Macro m105p02.sas !

hmlong25 — Sun, 16 Mar 2025 21:47:14 GMT

thank you careless errors

Re: Please help find why I cannot call this Macro m105p02.sas !

hmlong25 — Sun, 16 Mar 2025 21:48:36 GMT

Ok thank you that makes sense