topic Re: SAS macro variable warning in SAS Programming

SAS macro variable warning

sathya66 — Fri, 12 Feb 2021 12:15:08 GMT

All

Open Job from DI studio
Click on Run -> The job will run with a warning as mentioned below

Now only run transformation 3 -> It runs fine, and macro is resolved fine
Then run the whole job again -> It runs fine
Now close the job, open it again and run the whole job -> Same with a warning as above

Any idea? I thought it’s assigning a macro globally but 2nd step is working so its not a global macro issue.

Re: SAS macro variable warning

PaigeMiller — Fri, 12 Feb 2021 12:29:36 GMT

The macro variable &wt05_count does not exist. Then, somewhere along the way, the macro variable &wt05_count is created and you don't get the WARNING. When you create a new job, it doesn't know about the macro variables from other jobs. Other than that, we can't say as you have shown us only a small portion of the program.

Re: SAS macro variable warning

sathya66 — Fri, 12 Feb 2021 13:58:26 GMT

Thanks,

This is a simple user witten code in the user written transofrm . I tested once again and it looks like an issue if add a sub job(job in a job) if I add a sub job to a job then it is not working. If I remove the subjob then it is working.

nothing to do with subjob but not sure why macro is not resolving.

proc sql;
    connect to teradata  ( user=myusr1 password=mypwd1 );
    select * from connection to teradata 
	(select count(*) as count into:wt05_count from test)
	;
    disconnect from teradata;
    quit;
  %put &wt05_count

Re: SAS macro variable warning

japelin — Fri, 12 Feb 2021 15:35:20 GMT

If the wt05_count macro variable has been deleted in the subjob, or

if the wt05_count macro variable has been created in the subjob, then

it has become a local macro variable.

If it's the former, check the symdel and %symdel statements, and if it's the latter, try putting

%global wt05_count;

in front of the proc sql.

Re: SAS macro variable warning

Tom — Fri, 12 Feb 2021 15:45:50 GMT

That code looks wrong. It looks like you are trying to create a variable in Teradata instead of a macro variable in SAS.

Also when using INTO clause of PROC SQL to make a macro variable it is best to first set some default value for the macro variable in case the query returns no observations.

proc sql noprint;
  connect to teradata  ( user=myusr1 password=mypwd1 );
%let wt05_count=QUERY FAILURE;
  select count into :wt05_count  from connection to teradata 
    (select count(*) as count from test)
  ;
  disconnect from teradata;
quit;
%put &wt05_count

Re: SAS macro variable warning

sathya66 — Fri, 12 Feb 2021 16:16:59 GMT

Tried this
but no luck %global wt05_count;
let me test symdel and %symdel statements

Re: SAS macro variable warning

sathya66 — Fri, 12 Feb 2021 16:19:24 GMT

If we run only that trnasormation , it is resolving but with whole job it's not

only run transformation -> It runs fine, and macro is resolved fine
Then run the whole job again -> It runs fine
Now close the job, open it again and run the whole job -> Same with a warning

Re: SAS macro variable warning

sathya66 — Fri, 12 Feb 2021 16:48:26 GMT

also Just tried with simple proc sql and it didn't work.

libname test teradata AUTHDOMAIN="TeradataAuth" database=test fastload=yes;
proc sql noprint ;

select count(*) as count into:wt05_count from test.test;

quit;

I added a dummy job and run the code worked fine. If I add this subjob and Getting the warning. I think, the problem is with subjob.

Re: SAS macro variable warning

japelin — Fri, 12 Feb 2021 22:55:10 GMT

symdel deletes a macro variable.

Try searching for "symdel" in subjob.
If you find it, make sure you haven't deleted wt05_count.

Re: SAS macro variable warning

sathya66 — Wed, 17 Feb 2021 11:34:13 GMT

Recreated the main job , sub/inner job from scratch and working fine