topic Re: WARNING: Ignoring formula= could cause circular reference in excel in SAS Programming

WARNING: Ignoring formula= could cause circular reference in excel

bweeks59 — Thu, 27 May 2021 16:08:30 GMT

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

Re: WARNING: Ignoring formula= could cause circular reference in excel

Reeza — Thu, 27 May 2021 16:13:52 GMT

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?

Re: WARNING: Ignoring formula= could cause circular reference in excel

bweeks59 — Thu, 27 May 2021 16:19:26 GMT

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

Re: WARNING: Ignoring formula= could cause circular reference in excel

Reeza — Thu, 27 May 2021 16:22:43 GMT

Please show the code and log that generates the error.

Re: WARNING: Ignoring formula= could cause circular reference in excel

bweeks59 — Thu, 27 May 2021 16:28:58 GMT

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

Re: WARNING: Ignoring formula= could cause circular reference in excel

Reeza — Thu, 27 May 2021 16:30:51 GMT

No errors and no export code shown.

Re: WARNING: Ignoring formula= could cause circular reference in excel

bweeks59 — Thu, 27 May 2021 16:30:54 GMT

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

Re: WARNING: Ignoring formula= could cause circular reference in excel

Reeza — Thu, 27 May 2021 16:31:59 GMT

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

Re: WARNING: Ignoring formula= could cause circular reference in excel

bweeks59 — Thu, 27 May 2021 16:33:55 GMT

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;

Re: WARNING: Ignoring formula= could cause circular reference in excel

Reeza — Thu, 27 May 2021 16:37:52 GMT

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.

Re: WARNING: Ignoring formula= could cause circular reference in excel

bweeks59 — Thu, 27 May 2021 16:37:56 GMT

thanks for looking at this for me.