Hi All,
I'm running a batch process where I have to compare the old dataset with new dataset . I'm using proc compare to get the modified or newly added records from new to old but I want an update date in my dataset when ever there is any change in records.
eg:
data old; data new;
input ID Name$; input ID Name$;
cards; cards;
1 A 1 A
2 B 2 B
3 C 3 F
4 D 4 D
; RUN; 5 E;
run;
desired result:
ID Name$ EFFECTIVE DATE FROM EFFECTIVE DATE TO UPDATE DATE
1 A 23/03/2015 NULL NULL
2 B 23/03/2015 NULL NULL
3 F 23/03/2015 NULL 24/03/2015
4 D 23/03/2015 NULL NULL
5 E 24/03/2015 NULL NULL
Thank you
Sorry slight change in desired output:
desired output:
ID Name$ EFFECTIVE DATE FROM EFFECTIVE DATE TO UPDATE DATE
1 A 23/03/2015 NULL 23/03/2015
2 B 23/03/2015 NULL 23/03/2015
3 C 23/03/2015 24/03/2015 24/03/2015
3 F 24/03/2015 NULL 24/03/2015
4 D 23/03/2015 NULL 23/03/2015
5 E 24/03/2015 NULL 24/03/2015
Hi,
I would suggest your better off doing your data capture in a database. They are specifically built to have entry screens, update cycles, audit trails etc. There are lots of free ones out there.
If you really want to go down the route of doing this yourself, then its a fair bit of work. For instance you mention if a data item changes, what happens if it changes twice? What happens if the data item is moved in the sequence.
As for coding, simplest way would be to use SQL (which quite a lot of DB's use), you can then update tables based on criteria.
There are some functions in SAS which may help:
data old; input ID Name$; cards; 1 A 2 B 3 C 4 D ; RUN; data new; input ID Name$; cards; 1 A 2 B 3 F 4 D 5 E ; run; data want; merge old(in=ina) new(in=inb); by id name; if ina then old='23mar2015'd; if inb then new='24mar2015'd; if not inb then EFFECTIVE_DATE_TO='24mar2015'd; EFFECTIVE_DATE_FROM = coalesce(old,new); UPDATE_DATE = coalesce(EFFECTIVE_DATE_TO,EFFECTIVE_DATE_FROM); format old new EFFECTIVE_DATE_FROM EFFECTIVE_DATE_TO UPDATE_DATE date9.; drop old new; run;
Xia Keshan
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.