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 ...;
Hello from SAS Global Forum 2013!
SAS Global Forum 2013 is underway. So far we have focused on Visual Analytics and High Performance Analytics Server. All great advances in SAS computing power.
This conference is set to be a good one. If you weren't fortunate enough to make it to San Francisco this year, watch live via SAS stream.
http://support.sas.com/events/sasglobalforum/2013/live.html
Why Hadoop? From the DBA perspective…
We found this video from our friends at Cloudera pretty amusing. Hope you think so.
SAS Administration – Creating a client only software depot
If you need to get Enterprise Guide out to your users and you don't want them to have access to the rest of the SAS software, create a software depot on a network location that only has the clients included.
In order to do this you will need to do the following steps.
Navigate to your SAS Deployment Manager folder. Find the setup.exe (windows, .sh for linux, etc..).
setup.exe -subsetclients
By using this command line option, the normal 'by Foundation product' will become 'by product'. This will allow you to pick the clients you want included in the new depot.
Also note that there is a -subsetnonconfig option as well. This allows you to include foundation products and clients in a new depot.
See page 20 of this support doc for more details (SAS Support Doc).
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.
Creating all possible pairs / the Cartesian product
Ever since you started with SQL or learned SAS, you've been warned about Cartesian products. But sometimes you need them. Shocking, we know.
Quick note of warning, when creating all possible pairs, make sure you've narrowed down your dataset to only contain a distinct list of those values you want to use in the pairs. Extra values make it more likely that you'll take the system down...
Here are some easy ways to produce all possible pairs of data.
example 1 - Using dataset and point
data all_pairs;
set dataset_w_distinct_values;
do _n_=1 to nobs;
set value_y nobs=nobs point=_n_;
output;
end;
run;
example 2 - Using proc sql
proc sql;
create table all_pairs as
select
value_x, value_y
from
value_list_1, value_list_2;
quit;
example 3 - Using hash
data all_pairs (drop=_:);
if _n_=1 then do;
set dataset_w_distinct_values (obs=1);
dcl hash h(dataset: 'dataset_w_distinct_values', ordered: 'a');
h.definekey('value_x');
h.definedata('value_x');
h.definedone();
dcl hiter hi('h');
end;
set dataset_w_distinct_values ;
do _rc=hi.first() by 0 while (_rc=0);
output;
_rc=hi.next();
end;
run;
In database processing is great! (when you use it!)
Netezza and Teradata are both great examples of distributed processing databases that combine the power of SAS with their own platform to deliver a powerful analytics tool.
The problem? Clients rarely use it. We have seen several clients who possess this functionality. Some of who purchased the DB at the request of the analysts, only to see them never use the processing. The issues usually arise from old code, new analysts, ad-hoc scenarios, etc, etc.
If your organization is working with an in database system like Teradata or Netezza with SAS, let us know. We have put together a guide for analyst to use. This gives you code to borrow for the some of the most resource intensive SAS processing.
Here are a quick reference to some of the SAS docs:
Netezza - http://support.sas.com/documentation/onlinedoc/91pdf/sasdoc_913/access_netezza_9933.pdf
Teradata - http://support.sas.com/documentation/onlinedoc/91pdf/sasdoc_91/access_teradata_7370.pdf
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.
Measuring sentiment?
Often as marketers and analytics professionals, we are striving to measure every dollar. Yet, it may be equally important to get a read on things prior to the dollar figures. What about sentiment? Facebook knows. Google is learning. Pandora built a business around it. What about your org?
Sometimes we need to measure things faster and the dollars are more grey than black or white. Our advice? Improvise. Ask people what they think in 1 click. Yes or No? Like or dislike? Up or Down?
How is your organization leveraging sentiment to improve your marketing cycle?
Offers in the illusive REAL-TIME
Regardless of the industry or customer type, everywhere we go our discussions with clients gravitate towards the topic of real-time offers.
First, let me qualify what I mean by real-time.
Real-time is not:
- Picking from a group of offers available and presenting the one most relevant. There are many ways to accomplish this and although not every company has this ability, it is available.
- Putting an offer on your customer UI or website.
- Simply serving a predefined offer on a mobile device.
Real-time is augmenting an offer to be the right value proposition for a given customer at the time of interaction.
Example?
Customer X is going to make a purchase and is currently investigating offers from various organizations. Internally customer X knows that this purchase will bring him some measure of value. Customer X would purchase and commit to a 2 year contract with your company if you offered him 20% off instantly.
Customer Y is making the same purchase decision. Customer Y would also purchase from your company if you waived the startup costs however is unlikely to commit to more than 1 year of service.
Real-time is looking at historical patterns and augmenting the offer to meet the customers needs when a purchase decision is made. Predicting that Customer X should be offered 20% off for 2 years instead of 25% off.
The combination of profiling customers into complex sub groups based on historical preferences and performance and augmenting value propositions to meet the needs of the customer is much easier said than done. But companies that deliver this value and do it in multiple communication channels simultaneously take market share.
The first step to REAL-TIME is creating a partnership between your analytics group and technology teams. We stand in the gap between technology and analytics. We understand complicated data structures and the technology limits your organization has to work within.
If your organization is looking for ways to make a significant marketing impact with their marketing technology, contact us today.