I was wondering the best practice for moving data between Green Plum and unix base sas files on an integrated SAS grid environment. I have seen users use pg_dump or a copy to (format CSV) and then proc imported as SAS files. I have also seen the use of the odbc connection. I was looking for any feedback or if there were alternative methods to best optimize the use of the SAS Grid. Currently I am running small summarized files though ODBC Parallel processed like the following:
%let rc=%sysfunc(grdsvc_enable(_all_,server=SASApp_Grid));
options autosignon;
rsubmit task1 wait=no;
/*functions processed*/
endrsubmit;
/*example of odbc connect*/
proc sql;
connect to odbc(dsn = *** user=ME pwd="&dbpass");
insert into /*SAS premade table name*/_&snapdate.
select * from connection to odbc(
select *
From /*Green Plum Table*/
where date = %unquote(%str(%'&snapdate.%'))::date + interval '1 month' - interval '1 day'
;);
disconnect from odbc;
quit;
I then stack the tables and drop the monthly tables after moving the files (I am pulling by how the tables are partitioned on Green Plum). Should I continue this approach? Appreciate any help or feedback.
-Larri
If your current process works well why change it?
IMO SAS to database data transfers using SAS/ACCESS products are better than using intermediate text files because of the reliable data type translation of interfaces like ODBC. There is a lot less to go wrong with this approach.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.