topic Re: Using Proc Compare to compare datasets and adding Update date in SAS Procedures

Re: Using Proc Compare to compare datasets and adding Update date

BharathBandi — Mon, 23 Mar 2015 22:38:06 GMT

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

Re: Using Proc Compare to compare datasets and adding Update date

BharathBandi — Mon, 23 Mar 2015 22:51:06 GMT

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

Re: Using Proc Compare to compare datasets and adding Update date

RW9 — Tue, 24 Mar 2015 10:57:49 GMT

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:

Step-by-Step Programming with Base SAS(R) Software

Re: Using Proc Compare to compare datasets and adding Update date

Ksharp — Tue, 24 Mar 2015 12:59:02 GMT




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