BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
hmlong25
Obsidian | Level 7
 
%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

 

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller

View solution in original post

7 REPLIES 7
Kurt_Bremser
Super User

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

Bildschirmfoto 2020-04-07 um 08.32.59.jpg

 

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.

PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
Astounding
PROC Star

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

 

hmlong25
Obsidian | Level 7
thank you careless errors
Tom
Super User Tom
Super User

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.  

 

 

hmlong25
Obsidian | Level 7
Ok thank you that makes sense

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

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!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 1293 views
  • 1 like
  • 5 in conversation