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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.