I have a dataset that I created last week, and I have a new dataset that I created this week. I want to compare the two tables and only keep the observations in the new table that are either new or had a change. Is there a way to retain the ID values that correspond to any new or changed observations? For example, in the datasets below, I would like to create a new table that only keeps IDs 003 (added SEX and changed LASTVISIT), 005 (changed LASTVISIT), 006 (new observation), and 007 (new observation). I have looked through the documentation to see what my options are with output tables, but the output tables are not very usable for my needs.
data old;
input ID $ AGE SEX $ 9-10 LASTVISIT;
informat lastvisit date9.;
format lastvisit date9.;
cards;
001 37 M 15JAN2015
002 29 M 29JAN2015
003 64 12FEB2015
004 52 M 24FEB2015
005 49 F 20FEB2015
;
run;
data new;
input ID $ AGE SEX $ LASTVISIT;
informat lastvisit date9.;
format lastvisit date9.;
cards;
001 37 M 15JAN2015
002 29 M 29JAN2015
003 64 F 12FEB2015
004 52 M 24FEB2015
005 49 F 27FEB2015
006 50 F 26FEB2015
007 56 F 27FEB2015
;
run;
Desired Results:
ID | AGE | SEX | LASTVISIT |
---|---|---|---|
003 | 64 | F | 12FEB2015 |
005 | 49 | F | 27FEB2015 |
006 | 50 | F | 26FEB2015 |
007 | 56 | F | 27FEB2015 |
PROC COMPARE doesn't have a method that I know of, but you can certainly program it yourself. For example:
data want;
set old (in=in1) new (in=in2);
by id;
prior_age = lag(age);
prior_sex = lag(sex);
prior_lastvisit = lag(lastvisit);
if in2;
if (age ne prior_age) or (sex ne prior_sex) or (lastvisit ne prior_lastvisit);
* optionally: drop prior_:;
run;
The code assumes that both data sets are sorted (easy), but can become cumbersome to program if there are a lot of variables.
Good luck.
Yes, I have already written a program to compare them myself. I was just hoping for a more efficient way. But thank you anyway!
Please upvote this idea
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.