Proc SQL and the power of select into
I have referenced this paper many times. Great help in using the power of proc sql with macro variables. Great for building lists of values and dynamic code.
http://www.nesug.org/Proceedings/nesug97/coders/eddlesto.pdf
Syntax:
SELECT
object-item <,objectitem>...
<\ INTO macro-variablespecification <, macro-variablespecification> ...>
FROM from-list ...;
Exporting without double quotes
One of our junior programmers shot this one over after fighting with Proc Export for some time to remove the quotes from a string.
His need: export a single column of data without headers or quotes.
How he did it, quick and easy.
data _null_;
set inputdateset;
file "outputfilename";
put outputcolumnname;
run;
Sometimes the simple solutions are right in front of us.
Accessing files via SFTP in SAS
Secure File Transfer Protocol has become the standard for transferring files outside our organization. However, it is not always the easiest thing to do in SAS. Here are a few code examples for you to steal.
Importing a CSV file over SFTP:
%let host=afhood.com;
%let sftpOption=-o IdentityFile=/home/user/.ssh/id_rsa;
%let filename=theFile.csv;
%let sftpPath=/home/remoteuser/dir/;
filename myfile sftp "&sftpPath.&filename." host="&host." options="&sftpOption.";
proc import datafile= myfile
out=sftp_file
dbms=dlm
replace;
delimiter= ",";
getnames=yes;
run;
If you have any SFTP related questions, or need help building your automated file retrieval, let us know.
SAS Altlog option
If you run a production SAS environment or even a development environment for that matter, it is nice to have a specific location for all logs to be written and managed. ALTLOG helps achieve this.
The ALTLOG option can be set numerous ways, but we typically utilize 2.
1. As an option on the command line. Example:
sas sasprogram -altlog /my/log/dir/sasprogram_$timstamp\.log
2. Globally in the sas configuration file. This is wonderful as an administrator. All of the logs drop into one location for easy cleanup or archival. Users can specify an alternate location if they don't want it in the default, so everyone wins.
Making SOAP calls from SAS! Integrating with web services
One of the issues with SAS for many IT departments is the lack of integration with service oriented architecture (SOA). The good news is with many new features coming online with versions 9.X+ are service oriented.
Lets look quickly at the Proc SOAP procedure now available.
For those SAS programmers out there that aren't familiar with SOAP or services, get your basis here: SOAPUser-Basics
In a nutshell, SOAP is transporting XML data via a HTTP Post. In order to make a successful SOAP call from SAS you need a couple of things.
1. a request XML file
2. a repsonse XML file
3. a webservice URL and WSDL (Web Service Definition Language) -Think webservice users manual
Here is a simple example of a SOAP call we use on a daily job.
filename rqst_xml 'some file system reference';
* Create the XML;
data _null_;
set input_dataset;
file rqst_xml;
if first.records=1 then do;
put '<?xml version="1.0"?>
<soap:Envelope
xmlns:soap="http://www.w3.org/2001/12/soap-envelope"
soap:encodingStyle="http://www.w3.org/2001/12/soap-encoding"><soap:Header>
<requestingSystem>SAS</requestingSystem><requestingFunction>DemoSasScript</requestingFunction>
</soap:Header><soap:Body>
<requestedData>';end;
datasetData;
if last.records=1 then do;
put '</requestedData>
</soap:Body></soap:Envelope>';
end;
run;
filename rspns_xml 'some file system reference';
%let URL=http://webservice_url/service;
proc soap in=rqst_xml
out=rspns_xml
url=&url;
run;
Upon executing the call, you can read in the rspns_xml data with the SAS XML engine.
This is meant to be a simple example with very limited scope. Service architecture can quickly get complicated with error and condition handling. Please let us know if you need help with your SAS architecture or coding.
SQL tip – Inner join shorthand with USING
We write a LOT of SQL here and although SQL is a powerful database language, it can be tedious. So here is one tip for shortening all that typing.
Typical join sytax:
select some_columns
from one_table join another_table on one_table.column_1 = another_table.column_1 and one_table.column_2=another_table.column_2
where some_column > someother_column ;
Not too bad, right?
In order for this tip to work, you must be joining on columns with the same name (ie. column_1 and column_2 have the same name in both tables).
Here it is:
select some_columns
from one_table join another_table using (column_1, column_2)
where some_column > someother_column ;
Now, in our example it's not a drastic difference in coding, but as any programmer knows, this shorthand example doesn't accurately represent the real world. So give this syntax a try on your next project and let us know if it doesn't save you some typing.
SAS / Teradata Fastexport – dbsliceparm = all
Fastexport is the fastest way to get large data out of teradata. Fastexport utilizes multiple connections to deliver data and therefore speeding up the transfer of data between Teradata and SAS.
Here are a few examples of fastexport.
/* libname statement*/
libname teradb teradata username=&un password=&pw dbsliceparm=all;
/* explicit sql */
proc sql;
connect to teradata(username=&un password=&pw dbsliceparm=all);
<SQL STATMENT>
quit;run;
How do you know if fastexport was used?
Use this option:
options sastrace=',,,d' sastraceloc=saslog;
If it is working, you should see something in your log like:
Select was processed with fastexport.
There are many other factors that come into play if fastexport doesn't work. Check the requirements on the SAS support page for troubleshooting.
Default value to macro variable
NOTE: This is a great one we picked up from our friends over at the SAS community.
We have run across this literally hundreds of times while programming SAS macros. You need to have a default value for a variable and you don't want to write another macro to set it if it doesn't exist.
This simple fix allows you to check for a value and set a default even in open SAS code.
%global myParameter; /* ensure it exists */ %let myParameter = %sysfunc(coalescec(&myParameter,default-value));
Pure awesomeness..
Credit goes to Don Henderson and the SAS community for this one.
http://www.sascommunity.org/wiki/Tip_of_the_Day:October_12
SAS: Where Also
Ever heard of 'where also'? Neither did we.
We have to give credit to the guys at the SAS Community.
'Where also' allows you to add a series of where statements. The use acts like a single where statement with the and condition.
Example:
Data new_data;
set old_data;
where number=6;
where also another_number=.;
run;
This is the same as where number=6 and another_number=.
“call symputX” is the symput upgrade
SymputX is the upgrade from Call Symput.
Syntax:
call symputx("macro_var_name", character_value (or numeric to be converted to char), symbol table def);
Where symput will produce a note about converting character values to numeric, symputx won't produce such a note. Additionally it will strip leading and trailing spaces form the character value. Lastly, you can define which symbol table should be used (G=global, L=local, F=operates like symput).
data test;
set test_old;
call symput('variable_name', 'numeric value') /* gets a warning although it runs successfully*/;
call symputx('xvariable_name', 'numeric value') /* no warning for numeric conversion*/;
run;