1 The SAS System 10:30 Wednesday, May 13, 2020 NOTE: Unable to open SASUSER.PROFILE. WORK.PROFILE will be opened instead. NOTE: All profile changes will be lost at the end of the session. NOTE: Copyright (c) 2016 by SAS Institute Inc., Cary, NC, USA. NOTE: SAS (r) Proprietary Software 9.4 (TS1M6) Licensed to FEDEX CIA SRVR -TRANSATIONAL LICENSE SUPPLEMENT 53, Site 70024850. NOTE: This session is executing on the SunOS 5.10 (SUN 64) platform. NOTE: Analytical products: SAS/STAT 15.1 SAS/ETS 15.1 SAS/OR 15.1 SAS/IML 15.1 SAS/QC 15.1 NOTE: Additional host information: SUN SunOS SUN 64 5.10 Generic_150400-63 sun4v You are running SAS 9. Some SAS 8 files will be automatically converted by the V9 engine; others are incompatible. Please see http://support.sas.com/rnd/migration/planning/platform/64bit.html PROC MIGRATE will preserve current SAS file attributes and is recommended for converting all your SAS libraries from any SAS 8 release to SAS 9. For details and examples, please see http://support.sas.com/rnd/migration/index.html This message is contained in the SAS news file, and is presented upon initialization. Edit the file "news" in the "misc/base" directory to display site-specific news and information in the program log. The command line option "-nonews" will prevent this display. NOTE: SAS initialization used: real time 0.52 seconds cpu time 0.30 seconds 1 ******************************************************************************************; 2 options nocenter ps=max mlogic mprint; 3 ******************************************************************************************; 4 5 ******************************************************************************************; 6 * MACRO DRIVER ; 7 ******************************************************************************************; 8 %macro driver; 9 10 11 %inc "./common"; 12 13 14 proc sql; 15 connect to teradata (user=&ish_user password=&prsauto_pwd tdpid='edwapps.prod.xxxx.com' connection=global); 16 %do i=1 %to %sysfunc(countw(&permlist,%str( ))); 17 %let name=%scan(&permlist,&i,%str( )); 18 execute(grant select on prsauto_work_prod_db.jim_prcng_summary to &name) by teradata; 19 execute(commit) by teradata; 20 %end; 21 disconnect from teradata; 22 23 %mend; 24 ******************************************************************************************; 25 26 27 %driver; MLOGIC(DRIVER): Beginning execution. MPRINT(DRIVER): ******************************************************************************************; MPRINT(DRIVER): ******************************************************************************************; MPRINT(DRIVER): * Define the parameters describing the Teradata connection. ; MPRINT(DRIVER): ******************************************************************************************; MPRINT(DRIVER): ******************************************************************************************; MPRINT(DRIVER): proc sql; MPRINT(DRIVER): connect to teradata (user=prsauto password="xxxxxxxxxxxxxxxxxxxxx" tdpid='edwapps.prod.xxxxx.com' connection=global); MLOGIC(DRIVER): %DO loop beginning; index variable I; start value is 1; stop value is 8; by value is 1. MLOGIC(DRIVER): %LET (variable name is NAME) MPRINT(DRIVER): execute(grant select on prsauto_work_prod_db.jim_prcng_summary to MD5030147) by teradata; MPRINT(DRIVER): execute(commit) by teradata; MLOGIC(DRIVER): %DO loop index variable I is now 2; loop will iterate again. MLOGIC(DRIVER): %LET (variable name is NAME) MPRINT(DRIVER): execute(grant select on prsauto_work_prod_db.jim_prcng_summary to KA580944) by teradata; MPRINT(DRIVER): execute(commit) by teradata; MLOGIC(DRIVER): %DO loop index variable I is now 3; loop will iterate again. MLOGIC(DRIVER): %LET (variable name is NAME) MPRINT(DRIVER): execute(grant select on prsauto_work_prod_db.jim_prcng_summary to PRSAUTO) by teradata; MPRINT(DRIVER): execute(commit) by teradata; MLOGIC(DRIVER): %DO loop index variable I is now 4; loop will iterate again. MLOGIC(DRIVER): %LET (variable name is NAME) MPRINT(DRIVER): execute(grant select on prsauto_work_prod_db.jim_prcng_summary to CG482286) by teradata; MPRINT(DRIVER): execute(commit) by teradata; MLOGIC(DRIVER): %DO loop index variable I is now 5; loop will iterate again. MLOGIC(DRIVER): %LET (variable name is NAME) MPRINT(DRIVER): execute(grant select on prsauto_work_prod_db.jim_prcng_summary to NN1234567) by teradata; ERROR: Teradata execute: Database 'NN1234567' does not exist. NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. MPRINT(DRIVER): execute(commit) by teradata; NOTE: Statement not executed due to NOEXEC option. MLOGIC(DRIVER): %DO loop index variable I is now 6; loop will iterate again. MLOGIC(DRIVER): %LET (variable name is NAME) MPRINT(DRIVER): execute(grant select on prsauto_work_prod_db.jim_prcng_summary to KE436904) by teradata; NOTE: Statement not executed due to NOEXEC option. MPRINT(DRIVER): execute(commit) by teradata; NOTE: Statement not executed due to NOEXEC option. MLOGIC(DRIVER): %DO loop index variable I is now 7; loop will iterate again. MLOGIC(DRIVER): %LET (variable name is NAME) MPRINT(DRIVER): execute(grant select on prsauto_work_prod_db.jim_prcng_summary to KK5078688) by teradata; NOTE: Statement not executed due to NOEXEC option. MPRINT(DRIVER): execute(commit) by teradata; NOTE: Statement not executed due to NOEXEC option. MLOGIC(DRIVER): %DO loop index variable I is now 8; loop will iterate again. MLOGIC(DRIVER): %LET (variable name is NAME) MPRINT(DRIVER): execute(grant select on prsauto_work_prod_db.jim_prcng_summary to DT426854) by teradata; NOTE: Statement not executed due to NOEXEC option. MPRINT(DRIVER): execute(commit) by teradata; NOTE: Statement not executed due to NOEXEC option. MLOGIC(DRIVER): %DO loop index variable I is now 9; loop will not iterate again. MPRINT(DRIVER): disconnect from teradata; NOTE: Statement not executed due to NOEXEC option. MLOGIC(DRIVER): Ending execution. 40 NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 5.28 seconds cpu time 0.36 seconds ERROR: Errors printed on page 1. NOTE: SAS Institute Inc., SAS Campus Drive, Cary, NC USA 27513-2414 NOTE: The SAS System used: real time 5.95 seconds cpu time 0.75 seconds
I know my way around Oracle and DB2 pass-through and not really Terradata but I can imagine similar solutions work.
Create a SAS ACCESS library that references the same Terradata schema.
Then in your macro you can do something like:
%if %sysfunc(exist(terralib.&table) %then %do;
execute (
<Native terradata syntax that assumes &table exists>
) by terradata;
%end;
or you can query the terradata data dictionary to determine the existence of the tables.
Querying database dictionary tables can generally only be done using pass-through,
Likely you need to show us how the macro variable &permlist is created.
You are placing a name into that list that does not exist. So you need to revise the process that creates the list.
Note: it is often not a good idea to have macro variables just appear in the body of your macro instead of passing them explicitly as a parameter as debugging can get pretty hard in some cases.
%let permlist= MD5030147 KA580944 PRSAUTO CG482286 NN1234567 KE436904 KK5078688 DT426854;
Since you placed a value in a list, then what was the rule you used to add a non-existent file to the list?
Most DBMS systems have tools to get lists or create data sets with member, or properties of, data sets. (SAS Dictionary.tables for example) I would work with your Terradata DBA to get the syntax to build a set with the members you want (i.e. exist), read that data and use it to create the code you want. If you have the members you want in a data set it may be preferred to use that data with SAS call execute statement to create the calls you want.
I know my way around Oracle and DB2 pass-through and not really Terradata but I can imagine similar solutions work.
Create a SAS ACCESS library that references the same Terradata schema.
Then in your macro you can do something like:
%if %sysfunc(exist(terralib.&table) %then %do;
execute (
<Native terradata syntax that assumes &table exists>
) by terradata;
%end;
or you can query the terradata data dictionary to determine the existence of the tables.
Querying database dictionary tables can generally only be done using pass-through,
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.