Help using Base SAS procedures

Appending unequal table

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

Appending unequal table

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.


Accepted Solutions
Solution
‎07-18-2011 11:34 AM
PROC Star
Posts: 7,365

Appending unequal table

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.

View solution in original post


All Replies
PROC Star
Posts: 7,365

Appending unequal table

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

Occasional Contributor
Posts: 13

Appending unequal table

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

Solution
‎07-18-2011 11:34 AM
PROC Star
Posts: 7,365

Appending unequal table

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 154 views
  • 3 likes
  • 2 in conversation