Appending unequal table

[Osebeyo]

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.

[art297]

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

[Osebeyo]

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

1

2

3

4

5

5

6

7

7

7

[art297 · Accepted solution]

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.