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.
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.
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.
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";
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.
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.