topic Re: Convert Implicit SQL Pass-Through Code to Explicit and Keep Macro Variables in SAS Programming

Convert Implicit SQL Pass-Through Code to Explicit and Keep Macro Variables

ijm_wf — Tue, 28 Jan 2020 18:15:31 GMT

How can I rewrite the following implicit sql-pass through code making it explicit and keep the macro variables (&DEF_CNT.,&GHI_CNT.,&JKL_CNT.) for further use later in the code? Please help with code examples. Thanks.

options nofmterr symbolgen;

%let uid = "abc@LDAP";
%let pwd = "123";

libname datastore teradata server='xyz.company.com' schema="DATASTORE_V0" user=&uid pwd=&pwd.;

proc sql noprint;
select count(DEF_ID) into:DEF_CNT from DATASTORE.DEF;
%put &DEF_CNT;

select count(GHI_ID) into:GHI_CNT from DATASTORE.GHI;
%put &GHI_CNT;

select count(JKL_ID) into:JKL_CNT from DATASTORE.JKL;
%put &JKL_CNT;

quit;

Re: Convert Implicit SQL Pass-Through Code to Explicit and Keep Macro Variables

unison — Tue, 28 Jan 2020 18:20:46 GMT

@ijm_wf what happens when you move the put statements and execute them after you QUIT; proc sql?

Re: Convert Implicit SQL Pass-Through Code to Explicit and Keep Macro Variables

ijm_wf — Tue, 28 Jan 2020 18:37:11 GMT

The values are maintained.

Re: Convert Implicit SQL Pass-Through Code to Explicit and Keep Macro Variables

nirajs — Tue, 28 Jan 2020 19:14:37 GMT

@ijm_wf do you mean something like this?

options nofmterr symbolgen;

%let uid = "abc@LDAP";
%let pwd = "123";

proc sql noprint;
   connect to teradata
   (server='xyz.company.com' schema="DATASTORE_V0" user=&uid pwd=&pwd.);
   select count(DEF_ID) into :def_cnt
      from connection to teradata (
      select * from DATASTORE.DEF);
quit;

%put &def_cnt;

Re: Convert Implicit SQL Pass-Through Code to Explicit and Keep Macro Variables

nirajs — Tue, 28 Jan 2020 20:14:32 GMT

or better perform count in Teradata ,

options nofmterr symbolgen;

%let uid = "abc@LDAP";
%let pwd = "123";

proc sql noprint;
   connect to teradata
   (server='xyz.company.com' schema="DATASTORE_V0" user=&uid pwd=&pwd.);
   select cnt into :def_cnt
      from connection to teradata (
      select count(DEF_ID) as cnt from DATASTORE.DEF);
quit;

%put &def_cnt;

Does this help you accomplish what you are trying to do?