BookmarkSubscribeRSS Feed
BharathBandi
Calcite | Level 5

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

3 REPLIES 3
BharathBandi
Calcite | Level 5

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

Ksharp
Super User



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

sas-innovate-2024.png

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.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 774 views
  • 6 likes
  • 3 in conversation