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