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.
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.
Run this:
proc sql;
describe view SASLIB.SAS_IT_HPDESK;
quit;
and post the log from it.
Please use the [i] button for logs.
from SASLIB.SAS_IT_HPDESK
The view contains a circular reference to itself, which is not possible.
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.
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;
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.
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;
.....
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.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.