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

[ijm_wf]

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;

[unison]

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

[ijm_wf]

The values are maintained.

[nirajs]

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

[nirajs]

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?