So you are saying:
drop table tableName purge, does delete the Hive metadata as well as the HDFS file
drop table tableName, does delete the Hive metadata, BUT NOT the HDFS file
correct?
... View more
Yes, this code gets executed.
You do not need the RUN; statement, as you are only using macro statements.
I assume the macro variable SUM is defined as an output parameter.
Please note, that the %EVAL function will only work with integer values.
... View more
Find below a sample program that calculates the age in years months and days.
The sample is based on a SAS date value
data ageCalc;
infile cards dlm=",";
input
min_date : date9.
max_date : date9.
;
years = intck('year',min_date,max_date,'c');
months = intck('month',intnx('year',min_date,years,'same'),max_date,'c');
days = intck('day',intnx('month',intnx('year',min_date,years,'same'),months,'same'),max_date,'c');
format min_date max_date ddmmyyp10.;
cards;
10NOV1963,26sep2013
15mar1961,11oct2001
31jan2000,01mar2000
29feb2000,28feb2001
;
proc print data=ageCalc;
run;
... View more
Thanks for the feedback, since you use SQL pass through, we see exactly the same statement from the trace as the one you have written.
What impact did the "drop table ..." without the purge option have on the HDFS file, was it deleted as well?
When you use the Proc DELETE data=, a DROP TABLE statement will be passed to Hive.
... View more
Is your STP stored directly in the SAS Folder root?
Have a look at the following discussion, it contains a working example
https://communities.sas.com/t5/Administration-and-Deployment/REST-in-webservices-in-sas-9-4-m3/m-p/295647
... View more
What happens if you do the drop table without the purge? Again using SQL Passthrough
You can use the follwoing options to see which HiveQL is passed from to SAS to Hive:
options
sastrace=",,,d"
sastraceloc=saslog
nostsuffix
;
... View more
Use an array, so you can do the same operation on many variables.
see here for some more details https://support.sas.com/resources/papers/97529_Using_Arrays_in_SAS_Programming.pdf
... View more
To call a Stored Process using REST, the URL has to look like:
http://host:port/SASBIWS/rest/storedProcesses/stp_path
where stp_path is the full name your STP including any SAS folder names.
So your macro var SASBIWS_path should set to /SASBIWS/rest/storedProcesses
... View more
In my sample code the Hive table mycars is deleted in Hive metadata as well as in the HDFS.
Please show the output of the "describe formatted tableName" after you created the Hive table using the DATA Step
Also the output of the two HDFS LS= should show, that the actual data in HDFS gets deleted.
... View more
Use the SCAN function with a negative index to get the rightmost text and then use the INPUT function with the proper informat to convert the char value to a SAS date value.
... View more
In your code you use something like
proc sql;
select count(distinct(make)) into :n
from sashelp.cars;
quit;
%put NOTE: *&n*;
%let n = &n;
%put NOTE: *&n*;
when you look at the content of the macro var N, the first %PUT, you will see that you have leading blanks, they propably cause the error. A simple assignment statement will remove the leading blanks and everything will be fine.
Depending on your SAS version you might also be able to use the TRIMMED option after the :n
... View more
By default, tables created in Hive are managed tables. so if you delete a table from SAS using the methods you mentioned also the actual data in the HDFS is deleted.
Here is a code sample that will create a Hive table, show the metadata information from Hive, list the contents of the HDFS directory, delete, show HDFS directory info. You need to set the appropriate macro variables.
LIBNAME ahd HADOOP PORT=&hd_port SERVER="&hd_server" SCHEMA=&hd_schema;
proc delete data=ahd.mycars;
run;
data ahd.mycars;
set sashelp.cars;
run;
proc sql;
connect to hadoop (
PORT=&hd_port SERVER="&hd_server" SCHEMA=&hd_schema
);
select * from connection to hadoop (
describe formatted mycars
);
disconnect from hadoop;
quit;
options set=SAS_HADOOP_RESTFUL 1;
proc hadoop;
hdfs ls="&hive_db_location/mycars" ;
run;
options set=SAS_HADOOP_RESTFUL 0;
proc delete data=ahd.mycars;
run;
options set=SAS_HADOOP_RESTFUL 1;
proc hadoop;
hdfs ls="&hive_db_location" ;
run;
options set=SAS_HADOOP_RESTFUL 0;
... View more
If you need this information in the footnote statement, then I suggest you have a look at programming pattern described by @ChrisHemedinger, see his blog entries
http://blogs.sas.com/content/sasdummy/2012/03/20/sas-program-by-processing/
and
http://blogs.sas.com/content/sasdummy/2012/03/23/improving-on-a-sas-programming-pattern/
... View more
