topic Appending unequal table in SAS Procedures

Appending unequal table

Osebeyo — Mon, 18 Jul 2011 14:03:54 GMT

Hi,

I have three unequal tables (unequal number of column and rows) and hope to create a fourth table from appending these three unequal table. The fourth table will also have an added column, having binary number (0 or 1) based on this relationship; 1 when there is a user_id common to the three tables, 1 for all user_id in the action table and 0 for all user_id in impression and/or click not in action table.

I have attached a simple excel sheet containing all the three table and the expected output from the data manipulation process. I have been on this for over 72 hours and just couldn't figure out what i need to do.

Any help will be most appreciated.

Appending unequal table

art297 — Mon, 18 Jul 2011 14:41:14 GMT

If you don't have multiple entries in more than one of the tables, and you sort your 3 files by user_ID and Advertiser_ID, then you might be able to achieve what you want with something like:

data want;

merge impression (in=a) click(in=b) action(in=c);

by User_ID Advertiser_ID;

if c then common=1;

else common=0;

if missing(IP) then IP=0;

run;

Art

Appending unequal table

Osebeyo — Mon, 18 Jul 2011 15:29:40 GMT

Hello Art,

That was brilliant, many thanks for your response. Just a quick question, what will you suggest if there are multiple entries in one of the tables.

e.g

the user id in this form

Appending unequal table

art297 — Mon, 18 Jul 2011 15:34:15 GMT

One to many isn't a problem. Many to many is most easily accomplished with proc sql. Take a look at: http://support.sas.com/techsup/technote/ts705.pdf and the other references that article provides.