SAS macro variable warning

[sathya66]

All

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

1. Now only run transformation 3 -> It runs fine, and macro is resolved fine
2. Then run the whole job again -> It runs fine
3. 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.

[PaigeMiller]

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.

[sathya66]

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

[japelin]

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.

[sathya66]

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

[japelin]

symdel deletes a macro variable.

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

[Tom]

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

[sathya66]

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

[sathya66]

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.

[sathya66 · Accepted solution]

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