Creating a date delta (delta table/delta log) when updating dataset

[LiEnBe]

Hello, 

I have been using sas for a while for statistical and modelling purposes but am now venturing into a little more data wrangling than I am used to. 

I receive data (as excel files) four times a year and would like to be able to create a dataset with history, so that I can go back and redo my calculations later. 

I have done some datawork on the excel file and now have two datasets that looks something like this:

data have1;
   input ID:$3. date1:date9. amount:32.;
   format date1 date9.;
 datalines;
102 '02jun2020'd  163
103 '02jun2020'd  198
104 '02jun2020'd  155
;
run;

data have2;
   input ID:$3. date1:date9. amount:32.;
   format date1 date9.;
 datalines;
102 '14sep2020'd  163
103 '14sep2020'd  231
105 '14sep2020'd  116
;
run;

data want;
   input ID:$3. valid_from:date9. valid_to:date9. amount:32.;
   format valid_from valid_to date9.;
   datalines;
102 '02jun2020'd '31dec9999'd 163
103 '02jun2020'd '14sep2020'd 198
103 '14sep2020'd '31dec9999'd 231
104 '02jun2020'd '31dec9999'd 155
105 '14sep2020'd '31dec9999'd 116
;
run;

I have been looking at both the update statement and a little at sas macro programming, but have realized that I am a little out of clue as to where to start. Do any of you have an example or some resources I can look into? The valid to date can be blanc as well as having the max date.  

I am using SAS 9.4 

Thanks for your help.

[andreas_lds · Accepted solution]

You have to explain the logic you want applied.

To combine both datasets, you can use:

data want;
   merge have1(rename= (date1 = valid_from)) have2(rename= (date1 = valid_to));
   by ID;
run;

[LiEnBe]

Sorry for not explaining.

I think you got what I wanted though. Thank you.  

[LiEnBe]

I came up with this not very elegant solution, but it seems to work. better solutions or comments on this one are highly appreciated.

proc sql;
* Added rows;
create table added as
select b.ID
     , b.date1 as valid_from
	 , '31dec9999'd as valid_to
     , b.amount
     , "A" as changetype 
 from have2 b
 left outer join have1 a
  on a.ID = b.ID 
  where a.ID = ''
;

* not changed rows;
create table noChange as
select a.ID
     , a.date1 as valid_from
	 , '31dec9999'd as valid_to
     , a.amount
     , "N" as changetype 
 from have1 a
 left outer join have2 b
  on a.ID = b.ID 
  where (b.ID = '') 
   or (a.amount=b.amount)
;

* modified rows;
create table modified_before as
select a.ID
     , a.date1 as valid_from
	 , b.date1 as valid_to
	 , a.amount
	 , "M" as changetype
  from have1 a
  inner join have2 b
  on a.ID = b.ID
  where a.amount^=b.amount;


create table modified_after as
select b.ID
     , b.date1 as valid_from
	 , '31dec9999'd as valid_to
	 , b.amount
	 , "M" as changetype
  from have1 a
  inner join have2 b
  on a.ID = b.ID
  where a.amount^=b.amount;
quit;

data test4;
set added noChange modified_before modified_after;
run;