topic SAS Macro variable passing with Quations for ODBC table in New SAS User

SAS Macro variable passing with Quations for ODBC table

Ashpak — Tue, 23 Feb 2021 18:26:46 GMT

proc sql;

select count(*) into :cntr from NVPLocal.NVP_DOD_Catch ;

%put &cntr;

select distinct qrm_db,strategy,moddate into :Var1,:Var2,:Var3 from NVPLocal.NVP_DOD_Catch ;

%put &Var1;

%put &Var2;

%put &Var3;

run;

proc sql outobs=10;

connect to odbc (DSN=&DSN user=&user password=&pwds);

create table temp as select * from connection to odbc (

select * from [FASTControl].[dbo].[ALM_NVPData]

where qrm_db=&Var1

);

disconnect from odbc;

getting error message

ERROR: CLI describe error: [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'Jan21'. : [Microsoft][SQL

Server Native Client 11.0][SQL Server]Statement(s) could not be prepared.

Re: SAS Macro variable passing with Quations for ODBC table

PaigeMiller — Tue, 23 Feb 2021 18:31:12 GMT

First, write the code so that you can get it to work WITHOUT macro variables. Then, it should be relatively easy to get the code to work WITH macro variables. Show us that code that works without macro variables.

Also, when you get ERRORs, show us the LOG, the entire log for these two PROC SQLs, with nothing chopped out, in the order that it appears in the log.

Re: SAS Macro variable passing with Quations for ODBC table

Tom — Tue, 23 Feb 2021 18:39:51 GMT

If sounds like there is no variable named Jan21. Did you want use the value of VAR1 macro variable as a string literal?

If so why not just add the quotes when generating the macro variable. So if you did this:

select quote(trim(qrm_db),"'") into :Var1 from NVPLocal.NVP_DOD_Catch ;

Then VAR1 will contain 'Jan21' instead of just Jan21.

Re: SAS Macro variable passing with Quations for ODBC table

Ashpak — Tue, 23 Feb 2021 18:44:19 GMT

Thanks this got worked out.