Pulling Data from SQL Server Based on List of Values in SAS Table

[A_SAS_Man]

It did work initially, I was able to use the same values I'm attempting to use for this solution in the version that Kurt posted (albeit a smaller amount since his variable creation was limited to 65k length.) 

[Reeza]

Another way to debug it is to create the code into a variable rather than call execute, see the code generated to make sure it's valid and then re-wrap it with call execute. 

[ChrisNZ]

Here is a macro that does the heavy lifting, and splits and formats an in() clause:

[A_SAS_Man]

I am getting an error that reads "Incorrect syntax near '%'." with an additional warning that reads "WARNING: Apparent invocation of macro PUTLOG not resolved." I will continue to troubleshoot but let me know if you have any suggestions for me on that.

[ChrisNZ]

Oh yes. You also need this.

[A_SAS_Man]

Is there a piece of this macro that would be removing the periods from my database column names? I'm having an issue where periods are used to separate between table and element names in our warehouse, and when I input my variable name into the macro as table_name.element_name i get an error back saying that table_nameelement_name is not a valid column name. I can't seem to find where in the macro it would be carrying this out though to see if it's feasible to remove it.

[Tom]

Please show the actual code you are using.
Note that if you have a variable named TABLE with a value of table_name and you want to use it to construct table_name.column_name then you will need to type an extra period. That is because the period in &table.column_name is being used by the macro processor to detect that you want to reference the macro variable TABLE and not the macro variable TABLECOLUMN_NAME. So you will need to type &table..column_name to get a result of table_name.column_name.

[A_SAS_Man]

So I can't show my literal code as it contains our data warehouse information. I'm using the word "table" as an alias for the actual table name. The following is the exact code I'm using with the actual table names subbed out for aliases. 

So previously this is the format I was using. "core.db.table.element" is the format we need to refer to elements when pulling directly from the warehouse.

%set_in_clause(dsin=test,varout=core.db.table.element, varin=element,options=debug)

When trying your suggestion I tried the following and received the same error about invalid column name as before. 

%set_in_clause(dsin=test,varout=&core..db..table..element, varin=element,options=debug)

Edit: To clarify, I tried to provide a simpler example before by leaving out the "core" and "db" portions but the result is the same. It is removing all the periods between the different pieces of the element address and trying reference an element named "coredbtableelement" rather than core.db.table.element

Please let me know if that needs more clarification. 

[Tom]

That doesn't make any sense. Why did you add all of those extra periods that were not following any macro variable references?

[A_SAS_Man]

I maybe misunderstood your suggestion. So I don't need any extra periods if my reference is to "core.db.table.element"? Was that only if it began with the table?

[Tom]

You had a macro call where you passed in a string consisting of 4 words separated by periods as the value of the parameter.

A parameter to a macro is just a local macro variable.  So in the code of the macro where ever you reference &VAROUT it will be replaced with core.db.table.element.

In your next call you replaced the letter four character string core with a reference to a macro variable named CORE (note that you did not show any code setting any value to that new macro variable).  You also added some extra periods into the string.  Not sure why you did that.

I am not clear how any of that has anything to do with the question that prompted this sub thread which was why you were generating a string that appeared to be missing the period between the words.

It really helps a lot if you just play with it an see how it works so you can internalize it.  

Make a little program (or run it interactively if you have SAS display manager or SAS/Studio) that sets some macro variables and then just displays their values to see how it works.

%let dbname=core;
%let schema=db;
%let member=table;
%let varname=element;
%put DBNAME=&dbname;
%put DBNAME=&dbname.;
%put DBNAME=&dbname..;
%put WANT core.db.table.element;
%put HAVE &dbname..&schema..&member..&varname.; 
%put HAVE core.&schema..&member..&varname.; 
%put HAVE core.db.&member..&varname.; 

Try other combinations to see how expanding macro variables works.

[A_SAS_Man]

I am having a very hard time following you, I initially asked why the user created function that was supplied to me would remove periods between words within one of my inputs. In the example utilization of the SET_IN_CLAUSE macro there is no mention of formatting your input with an ampersand before the variable. Below is the example provided in the macro code.

proc sql;                                                                  
create table FILTERED as                                                         
select *                                                                      
from SASHELP.CLASS                                                            
where ( %set_in_clause(dsin=FILTER(firstobs=2), varin=ID, varout=AGE, maxnb=1000) );   
quit; 

When I follow that format I get the error I described in this subthread. So I am not following what your suggestion is. Are you familiar with the user macro I am using and are saying the documentation example on how to enter data into it is incorrect? Or are you telling me how to troubleshoot the results and figure out where in the macro the periods of my input are being removed?

proc sql;                                                                  
create table FILTERED as                                                         
select *                                                                      
from SASHELP.CLASS                                                            
where ( %set_in_clause(dsin=FILTER(firstobs=2), varin=core.db.table.element, varout=AGE, maxnb=1000) );   
quit; 

[Tom]

What is the macro you are calling? Where did it come from? Is it something you created? SAS created? Your company created? Is it something in this long thread?
If you don't have the code you could try turning on MPRINT, SYMBOLGEN and/or MLOGIC to try to figure out what the heck it is doing.

[A_SAS_Man]

It is the two macros initially posted by @ChrisNZ in this thread. The same person I responded to with my question about why the periods would be getting removed.

Edit: To clarify I think the issue I'm trying to solve is in the first macro he posted.

[A_SAS_Man]

So I think I was able to figure out a solution by modifying the macro, I'm not sure what all the ramifications of this would be yet so I'm still open to suggestions for improvement. But essentially there is one line of code in the macro:

%let varout =%sysfunc(compress(%superq(varout),,nk));

Which I modified to be:

  %let varout =%superq(varout);

And it seems to be working at this time.