BookmarkSubscribeRSS Feed
ijm_wf
Fluorite | Level 6

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;
4 REPLIES 4
unison
Lapis Lazuli | Level 10

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

-unison
ijm_wf
Fluorite | Level 6

The values are maintained.

nirajs
Fluorite | Level 6

@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
Fluorite | Level 6

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?

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 887 views
  • 0 likes
  • 3 in conversation