BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
singhashupratap
Fluorite | Level 6

Even in proc sql I'm not using noexex then again why getting error.
same sql query in executed on hive and it give correct result no syntax error.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
        58         disconnect from odbc;
NOTE: Statement not executed due to NOEXEC option.


    proc sql ;
    connect to odbc (dsn=inventory user=ashu password='');  
    create table libname.test_table as select 
    c0	format=11.,
    c1	format=$30. length=30,
    c2	format=11.,
    c3	format=11.2
    from connection to odbc
    (SELECT c0,c1,c2,c3
    FROM test_table 
    limit 10;);
    disconnect from odbc; 
    quit ;
1 ACCEPTED SOLUTION

Accepted Solutions
singhashupratap
Fluorite | Level 6
Solution for same mention db name in query like dbname.tablename it will run with out error

View solution in original post

11 REPLIES 11
singhashupratap
Fluorite | Level 6

@Kurt_Bremser before query it also show the below error then proc sql query
ERROR: CLI prepare error: [DSI] The error message HardyTCLIServiceClientStatusError could not be found in the en-US locale. Check
that /en-US/SimbaHiveODBCMessages.xml exists.

 

But issue is, this error comes in only new programs on same pattern previous created programs are running with any error.

Astounding
PROC Star

Looks like you have an extra semicolon after limit 10

singhashupratap
Fluorite | Level 6

I used semi colon before such way in past but never get error,but I also try after removing semi colon it revert same result Smiley Sad

Astounding
PROC Star

Then as KurtBremser suggested, you will have to show more of the log.  The key portions would occur just before the notes about the NOEXEC option being turned on.  There will be some clue as to why that option was turned on.

AndrewHowell
Moderator

As others have said, we need to see more of the preceeding log.

 

We don't need to see the ENTIRE log, but it would help if we could see the last successfully executed code, and everything after that, up to the "PROC SQL set option NOEXEC" message.

singhashupratap
Fluorite | Level 6

Complete log for same now this issue comes now for those report also which was runnig without error. before I never face

 

1                                                          The SAS System                                  16:29 Monday, May 9, 2016

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program';
4          %LET _CLIENTPROJECTPATH='C:\Users\goibibp-sas-admin\Desktop\In_Goibibo Reports\ingoibibo_inclusion.egp';
5          %LET _CLIENTPROJECTNAME='ingoibibo_inclusion.egp';
6          %LET _SASPROGRAMFILE=;
7          
8          ODS _ALL_ CLOSE;
9          OPTIONS DEV=ACTIVEX;
10         GOPTIONS XPIXELS=0 YPIXELS=0;
11         FILENAME EGSR TEMP;
12         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
13             STYLE=HtmlBlue
14             STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
15             NOGTITLE
16             NOGFOOTNOTE
17             GPATH=&sasworklocation
18             ENCODING=UTF8
19             options(rolap="on")
20         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
21         
22         GOPTIONS ACCESSIBLE;
23         libname inibibo '/sasdata/sasdemo1/ingoibibo';
NOTE: Libref INIBIBO was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /sasdata/sasdemo1/ingoibibo
24         
25         
26         proc sql;
27         connect to odbc (dsn=inv user=ashu password=XX);
ERROR: CLI error trying to establish connection: [unixODBC][DSI] The error message HardyHiveError could not be found in the en-US 
       locale. Check that /en-US/SimbaHiveODBCMessages.xml exists.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
28         create table inibibo.ingoibibo_inclusion as select
29         id	format=11.,
30         inclusionname	format=$50. length=50,
31         displayname	format=$50. length=50,
32         rateplan_id	format=11.,
33         inclusion_status	format=$20. length=20,
34         rateplan_status	format=$20. length=20
35         from connection to odbc
36         (SELECT inc.id, inc.inclusionname, inc.displayname, ri.rateplan_id,
37         (CASE WHEN  inc.isactive = true THEN 'Active Inclusion' WHEN  inc.isactive = false THEN 'Non Active Inclusion' END ) as
37       ! inclusion_status,
38         (CASE WHEN ri.inclusions_id = inc.id THEN 'With rate Plan'
39         WHEN  ri.inclusions_id  IS NULL THEN 'Without rate Plan' END) as rateplan_status
40         FROM goibibo_inventory.hotels_inclusions inc
41         LEFT JOIN goibibo_inventory.hotels_rateinclusions ri ON (ri.inclusions_id = inc.id ););
NOTE: Statement not executed due to NOEXEC option.
42         disconnect from odbc;
NOTE: Statement not executed due to NOEXEC option.
43         quit ;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.02 seconds
      
2                                                          The SAS System                                  16:29 Monday, May 9, 2016

44         /* creating index on exiting table
45         for more detail http://support.sas.com/documentation/cdl/en/sqlproc/62086/HTML/default/viewer.htm#a001395386.htm*/
46         /*proc sql;
47         create index createdon on inibibo.ingoibibo_hotel_booking(createdon);
48         quit;
49         */
50         
51         /* remove table from LasR server if exist */
52         %macro lasrDeletedIfExists(lib,name);
53         %if %sysfunc(exist(&lib..&name.)) %then %do;
54         	proc datasets library=&lib. nolist;
55         	delete &name.;
56         	quit;
57         %end;
58         %mend;
59         
60         /* Load data in LasR */
61         /* inTable : reference of datasource table (lib.table) */
62         /* lasRTable : reference of target table in lasR server (lib.table) */
63         /* lasrLabel : label to use for the lasr Table (users description) */
64         %macro loadDataInLasR(inTable,lasRTable,lasrLabel);
65         	proc options option=MEMSIZE;run;
66         	data VALIBLA.&lasRTable (label=&lasrLabel);set inibibo.&inTable;run;
67         %mend;
68         
69         /* Refresh metadata for the selected lasR table */
70         %macro updateMetadataForLasrTable(lasrLibref,mdsTargetFolder,lasrTableName);
71         proc metalib;
72              omr (LIBURI="SASLibrary?@libref='&lasrLibref'" );
73              /*no more used : update_rule=(delete);*/
74              report(type=summary);
75              folder="&mdsTargetFolder";
76              select=("&lasrTableName");
77         run;
78         %put PROC METALIB returned &SYSRC.;
79         %mend;
80         
81         
82         
83         /* Access the LASR library */
84         LIBNAME VALIBLA SASIOLA  TAG=HPS  PORT=10011 HOST="nmclbi01.nm.goibibo.com"
84       ! SIGNER="http://nmclbi01.nm.goibibo.com:7980/SASLASRAuthorization" ;
NOTE: Libref VALIBLA was successfully assigned as follows: 
      Engine:        SASIOLA 
      Physical Name: SAS LASR Analytic Server engine on local host, port 10011
85         
86         /* remove table from memory if loaded in lasr server
87         * Param1 : LASR libref
88         * Param2 : LASR table name
89         */
90         
91         %lasrDeletedIfExists(VALIBLA, ingoibibo_inclusion);



NOTE: Deleting VALIBLA.INGOIBIBO_INCLUSION (memtype=DATA).
NOTE: The table HPS.INGOIBIBO_INCLUSION has been removed from the SAS LASR Analytic Server.
NOTE: PROCEDURE DATASETS used (Total process time):
3                                                          The SAS System                                  16:29 Monday, May 9, 2016

      real time           0.70 seconds
      cpu time            0.03 seconds
      

92         
93         
94         /* Load data in LasR
95         * Param1 : local table to read
96         * Param2 : LASR table to push (write) in LASR server
97         * Param3 : Label to use for user description on the table
98         */
99         %let etls_endtime = %sysfunc(datetime(), datetime20.);
100        
101        %loadDataInLasR(ingoibibo_inclusion,ingoibibo_inclusion,Loaded on &etls_endtime.);

    SAS (r) Proprietary Software Release 9.4  TS1M3

 MEMSIZE=96745808640
                   Specifies the limit on the amount of virtual memory that can be used during a SAS session.
NOTE: PROCEDURE OPTIONS used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      


NOTE: There were 45690 observations read from the data set INIBIBO.INGOIBIBO_INCLUSION.
NOTE: The data set VALIBLA.INGOIBIBO_INCLUSION has 45690 observations and 6 variables.
NOTE: DATA statement used (Total process time):
      real time           1.58 seconds
      cpu time            0.05 seconds
      

102        
103        
104        /* Update metadata for the selected lasr table
105        * Param1 : LASR library libref
106        * Param2 : Target metadata server folder
107        * Param3 : LASR table name (only the table name, no libref)
108        */
109        %updateMetadataForLasrTable(VALIBLA,/Shared Data/SAS Visual Analytics/Public/LASR,ingoibibo_inclusion);

NOTE: A total of 1 tables were analyzed for library "Visual Analytics LASR".
NOTE: Metadata for 1 tables was updated.
NOTE: Metadata for 0 tables was added.
NOTE: Metadata for 0 tables matched the data sources.
NOTE: 0 tables listed in the SELECT or EXCLUDE statement were not found in either the metadata or the data source.
NOTE: 0 other tables were not processed due to error or UPDATE_RULE.
NOTE: PROCEDURE METALIB used (Total process time):
      real time           1.96 seconds
      cpu time            0.11 seconds
      

PROC METALIB returned 0
110        
111        
112        GOPTIONS NOACCESSIBLE;
113        %LET _CLIENTTASKLABEL=;
114        %LET _CLIENTPROJECTPATH=;
4                                                          The SAS System                                  16:29 Monday, May 9, 2016

115        %LET _CLIENTPROJECTNAME=;
116        %LET _SASPROGRAMFILE=;
117        
118        ;*';*";*/;quit;run;
119        ODS _ALL_ CLOSE;
120        
121        
122        QUIT; RUN;
123        
AndrewHowell
Moderator

Thanks for posting the full log.

 

Ok, your connection to your ODBC data source is failing, causing every subsequent SQL statement to be ignored.

 

You'll need to find out why the connection is failing:

  • User account and/or Password expired
  • Data source is missing
  • ODBC connection works on other machine but not this one - configuration issue

I've added a simple macro test to verify a successful connection.

 

proc sql;
	connect to odbc (dsn=inv user=ashu password=XX);
	%put &=SQLRC; /* Should be 0 */
	disconnect from odbc;
quit;
singhashupratap
Fluorite | Level 6
22 GOPTIONS ACCESSIBLE;
23 proc sql;
24 connect to odbc (dsn=inv user=ashu password=XX);
25 %put &=SQLRC; /* Should be 0 */
SQLRC=0
26 disconnect from odbc;
27 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.23 seconds
cpu time 0.01 seconds

it run with error and return SQLRC=0
AndrewHowell
Moderator

Curious - so the connect which previously failed, now works..

 

Next step is to see if you can create the target table..

 

create table inibibo.ingoibibo_inclusion
singhashupratap
Fluorite | Level 6
Solution for same mention db name in query like dbname.tablename it will run with out error

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 50863 views
  • 0 likes
  • 4 in conversation