Unable to convert Hive table to SAS table

[lydiawawa]

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!

[mkeintz · Accepted solution]

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.

[r_behata]

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;

[lydiawawa]

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..