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?
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.