thank you @SASKiwi, I will try this. ... View more

Thank you @LinusH , i can execute query in Hive but this source for us and we are doing etL into SAS. so i need to connection to Hadoop to access the data. thank you once again. ... View more

Hi,   following is n_row count and log  n_rows 40073670 ------------------------------------------------------------------------------------------------------------------------------------ -------------------------log----------------------------- 24 25 GOPTIONS ACCESSIBLE; 26 %let selstr = count(*) as n_rows; 27 proc sql; connect to hadoop ( server="XXXXXXXXXXXX" uri="jdbc:hive2://XXXXXX,XXXX,XXXX/; serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=XXXserver2" HIVE_KERBEROS_PRINCIPAL="hive/XXXXXX" port=xxxxx schema=xxxxx read_method=JDBC properties="mapred.job.queue.name=XXXX_default;hive.fetch.task.conversion=minimal;hive.cbo.enable=true; hive.compute.query.using.stats=true;hive.stats.fetch.column.stats=true;hive.stats.fetch.partition.stats=true; hive.prewarm.enabled=true;" dbmax_text=255 ); create table work.test as select * from connection to hadoop ( select &selstr. from &table. where to_date(process_date_time) >=&test_date_1. and to_date(process_date_time) <=&test_date_2. ); disconnect from hadoop; quit; NOTE: Compression was disabled for data set WORK.TEST because compression overhead would increase the size of the data set. NOTE: Table WORK.TEST created, with 1 rows and 1 columns. 51 ; 2 The SAS System 09:56 Tuesday, October 4, 2022 52 disconnect from hadoop; 53 quit; NOTE: PROCEDURE SQL used (Total process time): real time 17:21.22 user cpu time 0.12 seconds system cpu time 0.08 seconds memory 5598.90k OS Memory 43688.00k Timestamp 04/10/2022 11:18:42 AM Step Count 9 Switch Count 3 Page Faults 0 Page Reclaims 312 Page Swaps 0 Voluntary Context Switches 5065 Involuntary Context Switches 50 Block Input Operations 0 Block Output Operations 0 54 55 GOPTIONS NOACCESSIBLE; 56 %LET _CLIENTTASKLABEL=; 57 %LET _CLIENTPROCESSFLOWNAME=; 58 %LET _CLIENTPROJECTPATH=; 59 %LET _CLIENTPROJECTPATHHOST=; 60 %LET _CLIENTPROJECTNAME=; 61 %LET _SASPROGRAMFILE=; 62 %LET _SASPROGRAMFILEHOST=; 63 64 ;*';*";*/;quit;run; 65 ODS _ALL_ CLOSE; 66 67 68 QUIT; RUN; 69 ... View more

Hi, no there is not changing the variable into the &selstr. The selstr is limited for this query only. I am just copying the the processing time and number of observation below. please let me know how i can optimised it, i tried libname approach also but the still query  takes lot of time to extract data. ---------------------------------------------------------------------------------------------------------------------------- NOTE: Compressing data set WORK.test decreased size by 0.71 percent. Compressed is 58707 pages; un-compressed would require 59127 pages. NOTE: Table WORK.test created, with 43930975 rows and 13 columns. 45 ; 46 disconnect from hadoop; 47 quit; NOTE: PROCEDURE SQL used (Total process time): real time 35:37.20 2 The SAS System 09:28 Thursday, September 29, 2022 user cpu time 1:19.61 system cpu time 26.64 seconds memory 5633.62k OS Memory 43176.00k Timestamp 29/09/2022 03:46:38 PM Step Count 8 Switch Count 3 Page Faults 68 Page Reclaims 440 Page Swaps 0 Voluntary Context Switches 92058 Involuntary Context Switches 26290 Block Input Operations 0 Block Output Operations 0     ... View more

hank you Shumuel, but i need to calculate other date apart from 9999-99-99. I have tried lag function but not getting proper output ... View more

thank you everyone, I am able to read it successfully ... View more

Thank You so much for your time, I have tried through an SAS EG using import data option. and the following code has been generated. and I need same output data n SAS data set.   Input data ---------------- CountryISOCode_2 CountryISOCode_3 CountryName CurrencyCode Region CurrencySymbol AT AUT AUSTRIA EUR ACC € BE BEL BELGIUM EUR ACC € CZ CZE CZECH REPUBLIC CZK ACC Kč DE DEU GERMANY EUR ACC € ES ESP SPAIN EUR ACC € NL NLD HOLLAND NLG ACC ƒ  code generated through the the IMPORT DATA option in EG ------------------------------------------------------------------------------------------- DATA WORK.'Copy of EU_Company'n; 30 LENGTH 31 CountryISOCode_2 $ 2 32 CountryISOCode_3 $ 3 33 CountryName $ 14 34 CurrencyCode $ 3 35 Region $ 4 36 CurrencySymbol $ 2 ; 37 FORMAT 38 CountryISOCode_2 $CHAR2. 39 CountryISOCode_3 $CHAR3. 40 CountryName $CHAR14. 41 CurrencyCode $CHAR3. 42 Region $CHAR4. 43 CurrencySymbol $CHAR2. ; 44 INFORMAT 45 CountryISOCode_2 $CHAR2. 46 CountryISOCode_3 $CHAR3. 47 CountryName $CHAR14. 48 CurrencyCode $CHAR3. 49 Region $CHAR4. 50 CurrencySymbol $CHAR2. ; 51 INFILE '/sas/files/excel/#LN00018' 52 LRECL=33 53 ENCODING="LATIN1" 54 TERMSTR=CRLF 55 DLM='7F'x 56 MISSOVER 57 DSD ; 58 INPUT 2 The SAS System 01:24 Monday, January 25, 2021 59 CountryISOCode_2 : $CHAR2. 60 CountryISOCode_3 : $CHAR3. 61 CountryName : $CHAR14. 62 CurrencyCode : $CHAR3. 63 Region : $CHAR4. 64 CurrencySymbol : $CHAR2. ; 65 RUN; ... View more