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.
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.
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.