topic Re: Unable to convert Hive table to SAS table in SAS Programming

Unable to convert Hive table to SAS table

lydiawawa — Wed, 08 Jan 2020 15:33:13 GMT

Hi,

I'm trying to create row number in a Hive table and pull it to a SAS table, but for some reason the SAS table cannot be created. There are no error or warning. It is just after running, the SAS table does not exist and cannot be located in the defined library. However, if I do not have the code that creates row number before converting to SAS table, the table will be saved. Following is the code for creating row number and taking the first row within id groups on Hive table and convert it to SAS table:

proc sql;
connection using df20;
/*I omitted the code that created temp table id_f */

execute (create temporary table id_f1 as
  	select *
	from (
    select 
        *, ROW_NUMBER() OVER (partition by id) as rownum
    from id_f 
	
	) ranked
	where ranked.rownum = 1
by df20;


/*pull view of final id table to SAS server*/
create table myfolder.id_f2 as
	select * from connection to df20
		(select * from id_f1);
		
Disconnect from df20;


quit;

Appreciate any help!

Re: Unable to convert Hive table to SAS table

mkeintz — Wed, 08 Jan 2020 16:34:01 GMT

I don't think that PROC SQL has seen your QUIT statement. You have unbalanced parentheses (5 open parens and 4 close parens), so it looks like the QUIT is embedded in the EXECUTE clause.

Re: Unable to convert Hive table to SAS table

r_behata — Wed, 08 Jan 2020 16:46:29 GMT

Do you know the HDFS path for your Temp Schema ? Check to see if you notice any files there.

These options will enable additional additional details in your log.

options SASTRACE="ds" sastraceloc=saslog nostsuffix;
options msglevel=i;

Re: Unable to convert Hive table to SAS table

lydiawawa — Wed, 08 Jan 2020 18:14:25 GMT

Thank you all for looking into this case. It is caused by the missing parentheses, and I would definitely use @r_behata for checks. Sorry for the kinda "stupid" question..