A Blog on Analytics and Marketing

SAS, Marketing, Predictive Modeling, Statistics

Archive for September, 2008

Use SAS like FTP – PROC DOWNLOAND

Posted by phillippeng on September 29, 2008

Very often we need to work from home through VPN. The speed is fairly OK when you work on sas datasets through server. However, if you tried to move some non-sas files, it’s painfully slow. With PROC DOWNLOAD, you can use SAS as a ftp tool to move non-sas file much easier.

rsubmit;

filename source ‘/sourcefolder/myfile.pdf’;
filename dest ‘/destinationfolder/myfile.pdf’;


proc download infile=source outfile=dest binary; run;

endrsubmit;
signoff;

The BINARY option transfers files without any character translation (for example EBCDIC to ASCII) or insertion of record delimiters.

The simiar proc is PROC UPLOAD.

More information on this can be found from SAS site.

Posted in Uncategorized | Tagged: , , | Leave a Comment »

Use PROC SQL to merge large datasets – Nodupkey

Posted by phillippeng on September 29, 2008

Generally, we have two approaches to merge two datasets: merge statement in a data step and proc sql. Recently, I realized the advantages of using proc sql to merge large datasets.

Merge statement in a data step approach is simple, but it requires you to sort the data first. For a large data set, it may be very time consuming. For example, I have a data set with 33mm records. It took me almost one hour to only sort the data on the server. Using PROC SQL, it only took 25 minutes to finish the whole merge process.

In this post, I am going to summarize the PROC SQL way to achieve the nodupkey option in data step.

proc sql;
create table comb as
select distinct a.*, b.id
from one a right join two b
on a.id= b.id
quit;

If you try above code, you won’t get distinct match from dataset a as you use the nodupkey in the data step. The reason is that distinct option in proc sql is not the same as nodupkey but as nodup. In order to get the same results as nodupkey, we need to use an unknown function “monotonic()”. Here’s how:

proc sql;
create table comb as
select a.*,  b.response, b.id, monotonic() as rowid
from one a right join two b
on a.id = b.id
group by b.id
having rowid=min(rowid);
quit;

What the above code does is to find all the matches from table a for each id in table b and then group them by id. Using function monotonic(), we create a field rowid and then take only the first record (“having rowid = min(rowid)”). This works great when you merge large datasets.

Posted in SAS, SQL | Tagged: , , | 1 Comment »

Merkle’s Ethnic Dinner

Posted by phillippeng on September 28, 2008

Ethnic dinner has been a Merkle’s tradition for eight years. Every employee and their spouse are encouraged to brigh a dish representing their ethnic background to share together at the party.

The dinner was hosted in Tim’s house as usual this year. Although I heard that there may not be many Chinese show up in the party, I still made my way because I was trying to know more other co-workers.

Like many American friends I know, Tim lives in the village part. It’s about 30 minutes driving from my home. I am so grateful that GPS becomes popular nowadays. Without GPS, I don’t think I am confident in finding Tim’s home in the village during the evening.

There’s about 40 people show up in the party. Everybody seems having a good time. About 8 o’clock, we started to have our ethnic dinner. After Tim’s introduction, each one of us introduced ourselves, their group, and their dish. We had American, Germany, Jewish, Irish, English, Italian, Chinese (may be more). Both Patrick and another co-worker brought the German’s Shepherd’s pie. But obviously, their recipes are little bit different. Both are delicious. I brought an authentic Chinese dish: Chicken in wine sause. Direct translation may be “Drunken chicken”.

As I introduced myself, Darla came to my side and said to me: “Are you Phillip – Peng?” That’s how I met Darla in person the first time. We’ve been emailing each other several times on a project. She’s the account manager. But we never met in person before. We met at the party. Then I chatted with her and her handsome husband. That’s SO COOL.

About 10 o’clock, I had to say goodbye to Tim and his wife, and others so that I could go home to see my 6-weeks little son.

Posted in Social Events | Leave a Comment »