BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Osebeyo
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

3 REPLIES 3
art297
Opal | Level 21

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
Fluorite | Level 6

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
Opal | Level 21

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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