Can you create a Macro variable that's a comma delimited list?

[lawatkey]

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

[Kurt_Bremser · Accepted solution]

select distinct quote(trim(Var123)) into :MCVR separated by ',' from work.Table_A;

[lawatkey]

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.

[SASKiwi]

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 have a SQL Passthrough that I need to use to connect to a library, and within this passthrough, I can't call my work table to use as a filter on this column. Is there any other clever way around this?

[SASKiwi]

@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?

[lawatkey]

@SASKiwi 

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?

[SASKiwi]

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]

I tried it as you wrote it and got a "CREATE TABLE permission denied in database 'DB_1'.

[SASKiwi]

@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?

[ballardw]

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.

[lawatkey]

I'm not sure what the best approach is, but the external data set I am trying to draw from using my work.Table_A Var123 filter is HUGE, I need specific rows where a criteria is met, but this is maybe .001% of the entire table. The other issue is that the passthrough is much more efficient than accessing it through SAS directly, but I just want to figure out how I can bring this Var123 criteria into my passthrough.