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;
@ijm_wf what happens when you move the put statements and execute them after you QUIT; proc sql?
The values are maintained.
@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;
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?
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.