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.
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.
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
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
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.