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?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.