WARNING: Ignoring formula= could cause circular reference in excel

[bweeks59]

Hope someone can help me figure this out....I am getting a warning in code someone else wrote; so i am not exactly sure what they were trying to see if on condition_2  the value is missing then set the return to state null. Hope this makes sense; still new at SAS. I get returned information but if the value is missing it leaves it blank. 

How would I write this to get NULL instead of blank fields? 

Data work.conditions01;
      set  work.conditions;
	  if condition_2='' then condition_2='[null]';
	  else;
	  run;

WARNING: Ignoring formula = 2 OR REPCODE <> C AND SOURCE <> [null] OR, which otherwise might cause a circular

reference in Excel.

log file contains no errors

100   Data work.conditions01;
101        set  work.conditions;
102    if condition_2='' then condition_2='[null]';
103    else;
104    run;

NOTE: There were 828 observations read from the data set WORK.CONDITIONS.
NOTE: The data set WORK.CONDITIONS01 has 828 observations and 7 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

Excel output:

blank condition_2 should say null I believe???

Thanks in advance for you r time,

Becky

[Reeza]

If you view the SAS data set, conditions01m does it have the NULL value you'd like to see?

Show the screenshot of that table please.

Also, how did you export your data to Excel? Were there any warnings or notes?

[bweeks59]

the table shows null but the log gives me a warning and it does not print

[Reeza]

Please show the code and log that generates the error.

[bweeks59]

CODE:

**Pulls "fa" post rule conditions**;

Data work.conditions;
set almis_.fa_postcond;
where substr(tran_post_code,1,1) in &type and action_code in &action;
run;
Data work.conditions01;
set work.conditions;
if condition_2='' then condition_2='[null]';
else;
run;
Data work.conditions01;
set work.conditions01;
logic=catx(' ', condition_1, operator_1, condition_2, operator_2);
run;
Proc sort data=work.conditions01;
by action_code tran_post_code cond_order condition_1;
run;

*************************************************************************************************************

LOG FILE:

\

94   **Pulls "fa" post rule conditions**;
95  

6                                                          The SAS System                         Thursday, May 27, 2021 11:01:36 AM

96    Data work.conditions;
97        set almis_.fa_postcond;
98    where substr(tran_post_code,1,1) in &type and action_code in &action;
99    run;

NOTE: There were 828 observations read from the data set ALMIS_.FA_POSTCOND.
      WHERE SUBSTR(tran_post_code, 1, 1) in ('E', 'F', 'G', 'H') and action_code in (14, 16, 19, 20, 21, 23, 26, 28, 29, 30, 32,
      33, 34, 36, 39, 40, 106, 124, 153, 154);
NOTE: The data set WORK.CONDITIONS has 828 observations and 7 variables.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds
     

100   Data work.conditions01;
101        set  work.conditions;
102    if condition_2='' then condition_2='[null]';
103    else;
104    run;

NOTE: There were 828 observations read from the data set WORK.CONDITIONS.
NOTE: The data set WORK.CONDITIONS01 has 828 observations and 7 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
     

105  Data work.conditions01;
106       set work.conditions01;
107   logic=catx(' ', condition_1, operator_1, condition_2, operator_2);
108   run;

NOTE: There were 828 observations read from the data set WORK.CONDITIONS01.
NOTE: The data set WORK.CONDITIONS01 has 828 observations and 8 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
     

109  Proc sort data=work.conditions01;
110       by action_code tran_post_code cond_order condition_1;
111   run;

NOTE: There were 828 observations read from the data set WORK.CONDITIONS01.
NOTE: The data set WORK.CONDITIONS01 has 828 observations and 8 variables.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
     

[Reeza]

No errors and no export code shown.

[bweeks59]

I am unable top include the complete code set due to confidential information, thanks

[Reeza]

Then you'll need to work with SAS tech support.

[bweeks59]

thanks for looking at this for me. 

[bweeks59]

the log shows no errors

proc print statement:

ods excel options(sheet_name="conditions");
Proc print data=work.conditions style(header)=[backgroundcolor=lightblue fontfamily=times fontweight=bold] noobs;
run;

[Reeza · Accepted solution]

It shows a lot of warnings. Which field is generating the warnings? Drop variables one by one and see which one is the issue, though I suspect it's the LOGIC variable. Then check it via your data but doing it by rows. So do the first half of your data and see what errors are generated. If none are keep adding data until it generates and then isolate the records and identify your issue.

That's the best I can offer with what you're willing to share. Showing the code with the log is more helpful than just the warnings as SAS usually illustrates where the issue is in the log but you're not sharing the right sections.

Good Luck.