BookmarkSubscribeRSS Feed
merahilyana
Fluorite | Level 6

Here is my coding:

 

libname IPCSIMPA impala dsn = "impala_hadoop_ipcs" schema="myipcs_cdm_raw";

proc sql;
create table IPCSIMPA.E11
as select * from SASDATA.DATABASE_E1_RSTUDIO;
quit;

 

here is sas log:

 

1 ;*';*";*/;quit;run;
2 OPTIONS LS=64 PS=15;
3 OPTIONS PAGENO=MIN;
4 %LET _CLIENTTASKLABEL='Program (5)';
5 %LET _CLIENTPROCESSFLOWNAME='Process Flow';
6 %LET
6 ! _CLIENTPROJECTPATH='C:\Users\ipcs_sasdemo\Documents\M
6 ! y SAS Files\Danial_MyIPCS\Hadoop_trial.egp';
7 %LET
7 ! _CLIENTPROJECTPATHHOST='SASMYIPCSOA01.stats.gov.my';
8 %LET _CLIENTPROJECTNAME='Hadoop_trial.egp';
2 The SAS System
10:00 Thursday, January 9, 2020

9 %LET _SASPROGRAMFILE='';
10 %LET _SASPROGRAMFILEHOST='';
11
12 ODS _ALL_ CLOSE;
13 OPTIONS DEV=PNG;
14 GOPTIONS XPIXELS=0 YPIXELS=0;
15 FILENAME EGRTF TEMP;
16 ODS RTF(ID=EGRTF) FILE=EGRTF
17 ENCODING='utf-8'
18 STYLE=Journal2
19 NOGTITLE
20 NOGFOOTNOTE
3 The SAS System
10:00 Thursday, January 9, 2020

21 KEEPN
22 ;
NOTE: Writing RTF Body file: EGRTF
23 FILENAME EGSR TEMP;
24 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
25 STYLE=Journal2
26 STYLESHEET=(URL="file:///C:/Program%20Files/SASHo
26 ! me/SASEnterpriseGuide/7.1/Styles/Journal2.css")
27 NOGTITLE
28 NOGFOOTNOTE
29 GPATH=&sasworklocation
30 ENCODING=UTF8
4 The SAS System
10:00 Thursday, January 9, 2020

31 options(rolap="on")
32 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
33 FILENAME EGXLSSX TEMP;
34 ODS EXCEL(ID=EGXLSSX) FILE=EGXLSSX STYLE=Excel
35 OPTIONS (
36 EMBEDDED_TITLES="no" EMBEDDED_FOOTNOTES="no"
36 ! SHEET_INTERVAL="None"
37 );
38
39 GOPTIONS ACCESSIBLE;
40 /* Insert custom code before submitted code here */
5 The SAS System
10:00 Thursday, January 9, 2020

41 LIBNAME MYDATA 'C:\EG DATA';
NOTE: Library MYDATA does not exist.
42
43 libname IPCSIMPA impala dsn = "impala_hadoop_ipcs"
43 ! schema="myipcs_cdm_raw";
NOTE: Libref IPCSIMPA was successfully assigned as follows:
Engine: IMPALA
Physical Name: impala_hadoop_ipcs
44
45 proc sql;
46 create table IPCSIMPA.E15
47 as select * from SASDATA.DATABASE_E1_RSTUDIO;
6 The SAS System
10:00 Thursday, January 9, 2020

NOTE: SAS variable labels, formats, and lengths are not written
to DBMS tables.
NOTE: Table IPCSIMPA.E15 created, with 512 rows and 9 columns.

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

49
50 GOPTIONS NOACCESSIBLE;
7 The SAS System
10:00 Thursday, January 9, 2020

51 %LET _CLIENTTASKLABEL=;
52 %LET _CLIENTPROCESSFLOWNAME=;
53 %LET _CLIENTPROJECTPATH=;
54 %LET _CLIENTPROJECTPATHHOST=;
55 %LET _CLIENTPROJECTNAME=;
56 %LET _SASPROGRAMFILE=;
57 %LET _SASPROGRAMFILEHOST=;
58
59 ;*';*";*/;quit;run;
60 ODS _ALL_ CLOSE;
NOTE: Writing EXCEL(EGXLSSX) file:
C:\Users\IPCS_S~1\AppData\Local\Temp\SAS Temporary
8 The SAS System
10:00 Thursday, January 9, 2020

Files\_TD9736_SASMYIPCSOA01_\#LN01219
61
62
63 QUIT; RUN;

 

the picture is output.

Capture.JPG

 

what must i do?? 

2 REPLIES 2
Kalind_Patel
Lapis Lazuli | Level 10

Few things you can try to check the root cause:

 

1. Execute INVALIDATE METADATA statement in your Impala SQL Clients or using Proc SQL Explicit passthrough, sometimes you've to perform this step to get a change reflected in your session, for more details you can reference:
https://impala.apache.org/docs/build/html/topics/impala_invalidate_metadata.html

 

2. Check the table stats using Proc Contents 

 

3. Last thing is enable the DBMS specific logging, 

for example you can run this statement to get a detailed log about database activity:

options sastrace="d,d,d,d";

 

 

merahilyana
Fluorite | Level 6
ALL LIST YOU GIVE IS NOT HAVE PROBLEM AND I GET SOLVE MY PROBLEM. I HAVE ONE PROBLEM DATA APPEAR BUT NOT FULL ROW OF DATA APPEAR..

FOR EXAMPLE:
ORIGINAL ROW DATA HAVE 512
BUT OUTPUT IN IMPALA ROW DATA ONLY 176

WHY??

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 521 views
  • 0 likes
  • 2 in conversation