you are right, there were two chars of the whole string name different but I did not notice that before. In the initial stage I accidentely included empty tables which was consequently fixed.
You should check why tere are the two objects with same name but different type (data/view).
May be the empty data is a result of a bug or created unintentionally ?
If you are convinced that there must be both types with same name, you need
add to your macro checking the member type and how many observations there are,
but the - which of them is the right to use, if both - the data and the view - have real observations?
I have managed to make the script understand that it will only be executed for non-empty tables (views in my case). Now the whole piece of code works. Those of you that stated the script is fine were right, I just needed to exclude empty tables from the library and then run it. Thanks
Sounds like your problem was not with the macro variable generation and usage, but in the selection of which members to include in the list.
But you can simplify the way that you use SQL to generate the list of macro variables.
You do not need to know in advance the number of variables that it creates. Instead let PROC SQL tell you how many it finds.
Just leave the upper bound unspecified and save the automatic macro variable SQLOBS to see how many rows/macro variables were generated.
proc sql noprint;
select distinct table_name
into :var1-
from tables
;
%let nrows=&sqlobs ;
quit;
If you are running a really old version of SAS then you might need add an upper bound, but it just needs to be at least as large as the maximum number of rows you could get. SQL will only create the number of macro variables it actually needs.
proc sql noprint;
select distinct table_name
into :var1-:var99999
from tables
;
%let nrows=&sqlobs ;
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.