The current reference to SQL View has exceeded PROC SQL's limit of 50 reference

[smirockzz]

ERROR: The current reference to SQL View SASLIB.SAS_IT_HPDESK has exceeded PROC SQL's limit of 50 references to a single view in a SQL statement. Please check to make sure that these references are not part of a recursive situation.

ERROR: SQL View SASLIB.SAS_IT_HPDESK could not be processed because at least one of the data sets, or views, referenced directly (or indirectly) by it could not be located, or opened successfully.

The program is similar but it was working fine earlier but now giving an issue .

Kindly assist in resolving the issue . Have attached the program & log for your reference.

[Kurt_Bremser]

Please post code in a window opened with the "little running man", and logs in a window opened with the {i} button. Do NOT, I repeat NOT, use MS Office documents for this.

[Kurt_Bremser]

Run this:

proc sql;
describe view SASLIB.SAS_IT_HPDESK;
quit;

and post the log from it.

[smirockzz]

1 The SAS System
Wednesday, March 4, 2020 05:02:00 PM

1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Program';
4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5 %LET
_CLIENTPROJECTPATH='C:\Users\Administrator\Desktop\New_Project.egp';
6 %LET _CLIENTPROJECTPATHHOST='PSASPRD';
7 %LET _CLIENTPROJECTNAME='New_Project.egp';
8 %LET _SASPROGRAMFILE='';
9 %LET _SASPROGRAMFILEHOST='';
10
11 ODS _ALL_ CLOSE;
12 OPTIONS DEV=PNG;
13 GOPTIONS XPIXELS=0 YPIXELS=0;
14 FILENAME EGSR TEMP;
15 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16 STYLE=HTMLBlue
17
STYLESHEET=(URL="file:///D:/SASHome/SASEnterpriseGuide/7.1/Styles/HTMLBlue.css")
18 NOGTITLE
19 NOGFOOTNOTE
20 GPATH=&sasworklocation
21 ENCODING=UTF8
22 options(rolap="on")
23 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24
25 GOPTIONS ACCESSIBLE;
26 proc sql;
27 describe view SASLIB.SAS_IT_HPDESK;
NOTE: SQL view SASLIB.SAS_IT_HPDESK is defined as:

select SAS_IT_HPDESK.CALL_NUMBER as CALL_NUMBER format=$5.0
length=5, SAS_IT_HPDESK.EMPNAME as EMPNAME format=$27.0
length=27, SAS_IT_HPDESK.DEPARTMENT as DEPARTMENT format=$30.0 length=30,
SAS_IT_HPDESK.MOBILE as MOBILE format=$10.0 length=10,
SAS_IT_HPDESK.LOCATION as LOCATION format=$21.0 length=21,
SAS_IT_HPDESK.LOG_DATE as LOG_DATE format=$10.0 length=10,
SAS_IT_HPDESK.LOG_TIME as LOG_TIME format=$11.0 length=11,
SAS_IT_HPDESK.CLOSE_DATE as CLOSE_DATE format=$10.0 length=10,
SAS_IT_HPDESK.CLOSE_TIME as CLOSE_TIME format=$11.0 length=11,
SAS_IT_HPDESK.CATEGORY as CATEGORY format=$20.0 length=20,
SAS_IT_HPDESK.PROBLEM_DESCRIPTION as PROBLEM_DESCRIPTION format=$493.0
length=493, SAS_IT_HPDESK.RESOLVED as RESOLVED format=$1.0
length=1, SAS_IT_HPDESK.created_date as created_date format=DATE9.0
length=8, SAS_IT_HPDESK.Resolved_date as Resolved_date
format=DATE9.0 length=8, SAS_IT_HPDESK.updated_date as updated_date
format=DATE9.0 length=8, SAS_IT_HPDESK.Updated_time as
Updated_time format=TIMEAMPM8.0 length=8, SAS_IT_HPDESK.Ageing as Ageing
length=8, SAS_IT_HPDESK.Ageing_comp as Ageing_comp length=8
from SASLIB.SAS_IT_HPDESK SAS_IT_HPDESK;

28 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.03 seconds


29
30 GOPTIONS NOACCESSIBLE;
31 %LET _CLIENTTASKLABEL=;
32 %LET _CLIENTPROCESSFLOWNAME=;
33 %LET _CLIENTPROJECTPATH=;
34 %LET _CLIENTPROJECTPATHHOST=;
35 %LET _CLIENTPROJECTNAME=;
36 %LET _SASPROGRAMFILE=;
37 %LET _SASPROGRAMFILEHOST=;
38
39 ;*';*";*/;quit;run;
2 The SAS System
Wednesday, March 4, 2020 05:02:00 PM

40 ODS _ALL_ CLOSE;
41
42
43 QUIT; RUN;
44

[Kurt_Bremser]

Please use the [i] button for logs.

from SASLIB.SAS_IT_HPDESK

The view contains a circular reference to itself, which is not possible.

[smirockzz]

Hi ,

Can you assist in explaining that how can I do this " Please use the [i]
button for logs."

[Kurt_Bremser]

When posting in Rich Text mode, there is a row of icons at the top of the posting window; the sixth icon is the {i} button. The seventh is the "little running man", which opens an editor window with coloring similar to the Enhanced Editor in Enterprise Guide.

[Patrick]

@smirockzz 

Just in case you've missed the most important part for you in what @Kurt_Bremser wrote:

"The view contains a circular reference to itself, which is not possible."

That's the reason why things aren't working. Your SQL view calls itself which logically leads to an infinit loop.

Here some simple sample code to demonstrate the issue/wrong logic.

proc sql;
  create view vTest as
  select * from sashelp.class
  ;
quit;

proc sql;
  create view vTest as
  select * from vTest
  ;
quit;

proc print data=vTest;
run;

[smirockzz]

libname saslib "C:\SAS_LIB_DATA";

options validvarname=v7;
%let year =%sysfunc(today(),YEAR4.);
%let monthnum =%sysfunc(today(),MONTH2.);
%let daynum =%sysfunc(today(),DAY2.);
%let month=%sysfunc(putn(&monthnum, z2));
%let day=%sysfunc(putn(&daynum, z2));
%put &year &month &day;
%let path="D:\SAS EXCEL\IT\complaint 07.02.2018.xlsx";
%put &path;



/*D:\SAS EXCEL\IT\complaint 07.02.2018.xlsx*/
proc import datafile=&path
				out=IT_HP_DESK
				DBMS=xlsx replace;
				getnames=yes ;
run;


data HPDESk;
set IT_HP_DESK;
created_date=input(LOG_DATE,ddmmyy10.);
Resolved_date=input(CLOSE_DATE,ddmmyy10.);
updated_date=today();
Updated_time=time();
Ageing=INTCK('Day',created_date, updated_date);
Ageing_comp=INTCK('Day',created_date, Resolved_date);
format updated_date created_date Resolved_date date9. Updated_time timeampm8. ;
run;


proc sort data=HPDESk;
by CALL_NUMBER;
run;

proc sort data=saslib.SAS_IT_HPDESk;
by CALL_NUMBER;
run;

data saslib.SAS_IT_HPDESk;
UPDATE saslib.SAS_IT_HPDESk HPDESk;
BY CALL_NUMBER;
RUN;

1                                                          The SAS System                      Wednesday, March  4, 2020 05:02:00 PM

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='IT_HELP_DESK';
4          %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5          %LET _CLIENTPROJECTPATH='C:\Users\Administrator\Desktop\New_Project.egp';
6          %LET _CLIENTPROJECTPATHHOST='PSASPRD';
7          %LET _CLIENTPROJECTNAME='New_Project.egp';
8          %LET _SASPROGRAMFILE='D:\Programs\IT\IT_HELP_DESK.sas';
9          %LET _SASPROGRAMFILEHOST='PSASPRD';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=PNG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         FILENAME EGSR TEMP;
15         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16             STYLE=HTMLBlue
17             STYLESHEET=(URL="file:///D:/SASHome/SASEnterpriseGuide/7.1/Styles/HTMLBlue.css")
18             NOGTITLE
19             NOGFOOTNOTE
20             GPATH=&sasworklocation
21             ENCODING=UTF8
22             options(rolap="on")
23         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24         
25         GOPTIONS ACCESSIBLE;
26         libname saslib "C:\SAS_LIB_DATA";
NOTE: Libref SASLIB was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: C:\SAS_LIB_DATA
27         
28         options validvarname=v7;
29         %let year =%sysfunc(today(),YEAR4.);
30         %let monthnum =%sysfunc(today(),MONTH2.);
31         %let daynum =%sysfunc(today(),DAY2.);
32         %let month=%sysfunc(putn(&monthnum, z2));
33         %let day=%sysfunc(putn(&daynum, z2));
34         %put &year &month &day;
2020 03 11
35         %let path="D:\SAS EXCEL\IT\complaint 07.02.2018.xlsx";
36         %put &path;
"D:\SAS EXCEL\IT\complaint 07.02.2018.xlsx"
37         
38         
39         
40         /*D:\SAS EXCEL\IT\complaint 07.02.2018.xlsx*/
41         proc import datafile=&path
42         				out=IT_HP_DESK
43         				DBMS=xlsx replace;
44         				getnames=yes ;
45         run;

NOTE: One or more variables were converted because the data type is not supported by the V9 engine. For more details, run with 
      options MSGLEVEL=I.
NOTE: The import data set has 25 observations and 11 variables.
NOTE: WORK.IT_HP_DESK data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.02 seconds
2                                                          The SAS System                      Wednesday, March  4, 2020 05:02:00 PM

      cpu time            0.03 seconds
      

46         
47         
48         data HPDESk;
49         set IT_HP_DESK;
50         created_date=input(LOG_DATE,ddmmyy10.);
51         Resolved_date=input(CLOSE_DATE,ddmmyy10.);
52         updated_date=today();
53         Updated_time=time();
54         Ageing=INTCK('Day',created_date, updated_date);
55         Ageing_comp=INTCK('Day',created_date, Resolved_date);
56         format updated_date created_date Resolved_date date9. Updated_time timeampm8. ;
57         run;

NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
      51:21   
NOTE: Variable CLOSE_DATE is uninitialized.
NOTE: Missing values were generated as a result of performing an operation on missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      25 at 55:13   
NOTE: There were 25 observations read from the data set WORK.IT_HP_DESK.
NOTE: The data set WORK.HPDESK has 25 observations and 18 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
      

58         
59         
60         proc sort data=HPDESk;
61         by CALL_NUMBER;
62         run;

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

63         
64         proc sort data=saslib.SAS_IT_HPDESk;
ERROR: The current reference to SQL View SASLIB.SAS_IT_HPDESK has exceeded PROC SQL's limit of 50 references to a single view in a 
       SQL statement. Please check to make sure that these references are not part of a recursive situation.
ERROR: SQL View SASLIB.SAS_IT_HPDESK could not be processed because at least one of the data sets, or views, referenced directly 
       (or indirectly) by it could not be located, or opened successfully.
65         by CALL_NUMBER;
66         run;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.03 seconds
      cpu time            0.03 seconds
      
67         

3                                                          The SAS System                      Wednesday, March  4, 2020 05:02:00 PM


68         data saslib.SAS_IT_HPDESk;
69         UPDATE saslib.SAS_IT_HPDESk HPDESk;
ERROR: The current reference to SQL View SASLIB.SAS_IT_HPDESK has exceeded PROC SQL's limit of 50 references to a single view in a 
       SQL statement. Please check to make sure that these references are not part of a recursive situation.
ERROR: SQL View SASLIB.SAS_IT_HPDESK could not be processed because at least one of the data sets, or views, referenced directly 
       (or indirectly) by it could not be located, or opened successfully.
70         BY CALL_NUMBER;
71         RUN;

ERROR: Unable to create SASLIB.SAS_IT_HPDESK.DATA because SASLIB.SAS_IT_HPDESK.VIEW already exists.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      cpu time            0.04 seconds
      

72         
73         
74         
75         
76         
77         GOPTIONS NOACCESSIBLE;
78         %LET _CLIENTTASKLABEL=;
79         %LET _CLIENTPROCESSFLOWNAME=;
80         %LET _CLIENTPROJECTPATH=;
81         %LET _CLIENTPROJECTPATHHOST=;
82         %LET _CLIENTPROJECTNAME=;
83         %LET _SASPROGRAMFILE=;
84         %LET _SASPROGRAMFILEHOST=;
85         
86         ;*';*";*/;quit;run;
87         ODS _ALL_ CLOSE;
88         
89         
90         QUIT; RUN;
91         




Log after adding the code asked


1  The SAS System     Wednesday, March  4, 2020 05:02:00 PM

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program';
4          %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5          %LET _CLIENTPROJECTPATH='C:\Users\Administrator\Desktop\New_Project.egp';
6          %LET _CLIENTPROJECTPATHHOST='PSASPRD';
7          %LET _CLIENTPROJECTNAME='New_Project.egp';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=PNG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         FILENAME EGSR TEMP;
15         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16             STYLE=HTMLBlue
17             STYLESHEET=(URL="file:///D:/SASHome/SASEnterpriseGuide/7.1/Styles/HTMLBlue.css")
18             NOGTITLE
19             NOGFOOTNOTE
20             GPATH=&sasworklocation
21             ENCODING=UTF8
22             options(rolap="on")
23         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24         
25         GOPTIONS ACCESSIBLE;
26         proc sql;
27         describe view SASLIB.SAS_IT_HPDESK;
NOTE: SQL view SASLIB.SAS_IT_HPDESK is defined as:

        select SAS_IT_HPDESK.CALL_NUMBER as CALL_NUMBER format=$5.0 length=5, SAS_IT_HPDESK.EMPNAME as EMPNAME format=$27.0 
length=27, SAS_IT_HPDESK.DEPARTMENT as DEPARTMENT format=$30.0 length=30, SAS_IT_HPDESK.MOBILE as MOBILE format=$10.0 length=10, 
SAS_IT_HPDESK.LOCATION as LOCATION format=$21.0 length=21, SAS_IT_HPDESK.LOG_DATE as LOG_DATE format=$10.0 length=10, 
SAS_IT_HPDESK.LOG_TIME as LOG_TIME format=$11.0 length=11, SAS_IT_HPDESK.CLOSE_DATE as CLOSE_DATE format=$10.0 length=10, 
SAS_IT_HPDESK.CLOSE_TIME as CLOSE_TIME format=$11.0 length=11, SAS_IT_HPDESK.CATEGORY as CATEGORY format=$20.0 length=20, 
SAS_IT_HPDESK.PROBLEM_DESCRIPTION as PROBLEM_DESCRIPTION format=$493.0 length=493, SAS_IT_HPDESK.RESOLVED as RESOLVED format=$1.0 
length=1, SAS_IT_HPDESK.created_date as created_date format=DATE9.0 length=8, SAS_IT_HPDESK.Resolved_date as Resolved_date 
format=DATE9.0 length=8, SAS_IT_HPDESK.updated_date as updated_date format=DATE9.0 length=8, SAS_IT_HPDESK.Updated_time as 
Updated_time format=TIMEAMPM8.0 length=8, SAS_IT_HPDESK.Ageing as Ageing length=8, SAS_IT_HPDESK.Ageing_comp as Ageing_comp length=8
          from SASLIB.SAS_IT_HPDESK SAS_IT_HPDESK;

28         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.02 seconds
      cpu time            0.00 seconds
      

29         
30         GOPTIONS NOACCESSIBLE;
31         %LET _CLIENTTASKLABEL=;
32         %LET _CLIENTPROCESSFLOWNAME=;
33         %LET _CLIENTPROJECTPATH=;
34         %LET _CLIENTPROJECTPATHHOST=;
35         %LET _CLIENTPROJECTNAME=;
36         %LET _SASPROGRAMFILE=;
37         %LET _SASPROGRAMFILEHOST=;
38         
39         ;*';*";*/;quit;run;
2                                                          The SAS System                      Wednesday, March  4, 2020 05:02:00 PM

40         ODS _ALL_ CLOSE;
41         
42         
43         QUIT; RUN;
44         

But if you can see in my code I have not used SQL procedure anywhere so why is it giving this issue.

[Patrick]

You have in your code the following rather at the beginning:

proc sort data=saslib.SAS_IT_HPDESk;
   by CALL_NUMBER;
run;

What the SAS Log tells us: saslib.SAS_IT_HPDESk is not a table but a view. Furthermore this view is not valid.

It's likely that another program created the view. You're just trying to use it. Think of a view as encapsulated SQL code. The Create View statement only creates the view (saves the SQL code). The actual code gets only executed if you try to use the view. 

If you look at the sample code I've posted the following doesn't throw an error as it only creates the view but doesn't execute it.

proc sql;
  create view vTest as
  select * from sashelp.class
  ;
quit;

proc sql;
  create view vTest as
  select * from vTest
  ;
quit;

Only when you try to access the view the SQL gets executed and the error gets thrown.

proc print data=vTest;
run;

...and that's exactly what happens to you in the Proc Sort step. You're accessing the view as source for sorting.

Ideally the SAS SQL compiler would already throw an error when you try to compile an invalid view - but it doesn't in this case.

Soo... If it wasn't you who created the view then talk to the person who did because the SQL of this view is wrong.

And last but not least: I believe you can't update a physical SAS table via a SAS view (some database views would allow for such things). Given that saslib.SAS_IT_HPDESk is not a table but a view I believe even if the view definition would be valid, below wouldn't work.

data saslib.SAS_IT_HPDESk;
 UPDATE saslib.SAS_IT_HPDESk HPDESk;
.....

[Kurt_Bremser]

You use SQL implicitly, because the "dataset" you use is in fact a SQL view. And that view is incorrectly specified, leading to a runtime ERROR whenever it is called. You need to get back to the one who created that view; as it is, it can not work, and it is important to know what it should achieve, or if the whole thing is just a mistake.