Help using Base SAS procedures

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

Reply
Contributor
Posts: 34

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

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

Contributor
Posts: 34

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

Posted in reply to BharathBandi

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

Super User
Super User
Posts: 7,942

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

Posted in reply to BharathBandi

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

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

Super User
Posts: 10,023

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

Posted in reply to BharathBandi



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

Ask a Question
Discussion stats
  • 3 replies
  • 222 views
  • 6 likes
  • 3 in conversation