For a simple example, I have the following column in a table, Table_A:
Var123 |
ABC |
EFG |
KLM |
XYZ |
Is there a way to insert these into a macro variable that ends up looking like this?
'ABC','EFG','KLM','XYZ'
Reason: I am running a program through a SQL pass through where I can't join to other tables in Work, like Table_A. I can still call macro variables inside of the SQL pass through, so I thought inserting that column into a macro variable would be a clever solution. The only thing I can think of is running the program through a loop for each unique result in that column "Var123", but in my actual data there are a few thousand rows, so I'm not sure that's efficient. If something better exists, I would love to hear about it!
"select distinct trim(Var123) into :MCVR from work.Table_A;" works in and of itself, but it seems to just put the first row. I can do a count distinct into a macro variable, and then use that count variable to insert into :MCVR1 - :MCVR&COUNT but that would just leave me with the option of looping the variable &COUNT many times in the SQL passthrough program, right?
The end goal is, within the SQL passthrough program, to use a where expression like, "where tablevariable in (&MCVR)"
select distinct quote(trim(Var123)) into :MCVR separated by ',' from work.Table_A;
select distinct quote(trim(Var123)) into :MCVR separated by ',' from work.Table_A;
This is perfect, thank you!! As another comment mentioned, I ran into size issues though (just barely but still...), should I make another thread to ask about this? Like, I have a SQL Passthrough program that doesn't allow me to join to Work tables, do you know a clever way I can call or filter on the work table within a passthrough statement? Either way, your reply was exactly what I was looking for, I just didn't anticipate the size issue.
The maximum length of a SAS macro variable is 32K. If each string value is 6 characters, including quotes and comma, that means you can hold 5461 values.
What the problem with loading a temporary database table? Most databases allow this.
@lawatkey - I would load my SAS lookup table into the external database's temporary database and join to the required table to be filtered there. It's by far the most robust and scaleable solution. What is your problem with this approach?
proc sql;
connect to sqlsvr(complete="Driver=SAS etc; Authentication=9;Database=DB_1;HostName=Host_1;Port=Port_1;Domain=Domain_1; Logon/PW;");
create table work.want as select distinct * from connection to sqlsvr(
select *
from DB_1.dbo.external table where some_variable in (select Var123 from work.Table_A););
quit;
Given the above as a template that is basically what I am trying to do, where am I trying to insert a table? I don't have write access to DB_1 in the above example, if that's what you mean, so I can't put a table there, unless there is a way to do so temporarily?
Something like this should work to load your SAS lookup table. Then you can join to it in a PASSTHRU query.
libname TEMPDB sqlsrvr complete="Driver=SAS etc; Authentication=9;Database=DB_1;HostName=Host_1;Port=Port_1;Domain=Domain_1; Logon/PW;" qualifier = TEMPDB;
data TEMPDB.MySASLookupTable; * SQL Server tempdb table;
set WORK.MySASLookupTable; * SAS lookup table;
run;
@lawatkey - Every SQL Server database server has a database called TEMPDB (it won't be any other name) which is specifically designed for loading temporary tables. It looks like the connection string is not pointing you at TEMPDB but DB_1 instead. Please check with your SQL Server administrator to confirm the correct connection string for accessing TEMPDB and to confirm your user account has permissions to use it.
Edit: Try Database=tempdb in your connection string?
One question though is "should you create comma delimited list of macro variables".
One reason to ask is if you use this with other macros the commas can cause issues because they are the delimiter of macro functions such as %scan.
Second, how will you use this list? Every so often we get questions where someone wants to stick a lot of data into macro variables instead of using approaches like combining data sets.
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 16. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: