Error using instream feature for input parameters with SAS Excel Add-in

[kjarvis]

Hello,

I'm trying to use SAS Add-in for Excel with a stored process having one input parameter. My customer would like the ability to enter multiple input parameters at a time (i.e. copy and paste 30 SSNs at once instead of entering one at a time with the input parameter box).

I discovered that I can use the instream feature with the stored process. I've got it set up so that the add-in allows me to select the list of SSNs - but then this error arises:

WARNING: Apparent symbolic reference SSNLIST not resolved.

ERROR: CLI describe error: Microsoft ODBC SQL Server Driver SQL Server Invalid column name '&SSNList'. : Microsoft ODBC SQL Server Driver SQL Server Statement(s) could not be prepared.

Here is my stored process code:

--------------------------------------------------------------------------------

libname Instr xml;

data work.SSNList;
  set Instr.&_webin_sasname;
run;

proc sql;
connect to odbc ("dsn=SQL server DW; Trusted_Connection=yes; database=DW");  

select * from connection to odbc
(SELECT t1.IP_Key, 
                t1.client_address,
                t1.createddate,
                t1.confirm_num,
                t1.ssn, 
    FROM Test1.XXX_Address_VW t1
  WHERE t1.SSN IN ("&SSNList")
);

disconnect from odbc;

QUIT;

-------------------------------------------------------------------------------------

I know that the error is with "&SSNList" - but what should it be to use the instream feature?

Thank you for any suggestions.

[kjarvis]

I should have probably used the words "input stream" instead of "instream". Sorry for any confusion!

[sustagens]

The data from your input stream is stored in a dataset, not a macro variable.

When you add an ampersand - &SSNList - you are referencing a macro variable, which in your case doesn't exist.

You can alter your code so that the filter selects all the contents of your dataset, SSNList.

One way is by editing your where statement with a subquery like:

WHERE t1.SSN IN (select [insert name of your SSN column from the input stream] from SSNList)

Don't forget to include the headers in your input stream, when you specify the range in Excel.

Alternately you can do it with a join:

FROM Test1.XXX_Address_VW t1 left join SSNList t2 on 
t1.SSN=t2.[insert name of your SSN column from the input stream]