running total of transactions with history

[rachatu]

Hello community, Here's a tricky one and I need some help please. I have a current table of transactions. My requirement is to maintain a history of transactions for every user. I need to leave out the latest from current transactions and backup all previous. In the history table I would also like to maintain a count of the number of transactions that occurred on that day, total transactions as of that day for a user and previous transaction date for each record in history table.

An example is below. Any help is much appreciated. 

current
id	dt	val
1	3/5/19	Test
1	3/10/19	Test
1	3/10/19	Test
1	3/15/19	Test
2	1/14/19	Test
3	1/14/19	Test
3	1/12/19	Test
history
id	dt	val	prev_Dt	volume	tot_as_of_today
1	3/10/19	Test	3/5/19	2	3
1	3/5/19	Test	null	1	1
3	1/12/19	Test	null	1	1

[PeterClemmensen]

Hi and welcome to the SAS Communites 🙂

Just to be clear on the logic: Why does id=2 not appear in the history table?

[rachatu]

Hello! It's not in history because we do not capture latest transactions in history. All transaction in history would be older than the latest one. 

[ballardw]

Does VAL every have a value other than "Test". If so you should show some input examples and how the output may change.

If it doesn't change, why is it there at all?

I am not sure what your tot_as_of_today variable represents. Unless a better name would be tot_as_of_dt?

This creates a matching data set to the required basically.

data have;
input id dt :mmddyy8. val  $;
format dt mmddyys8.;
datalines;
1 3/5/19 Test       
1 3/10/19 Test       
1 3/10/19 Test       
1 3/15/19 Test       
2 1/14/19 Test       
3 1/14/19 Test       
3 1/12/19 Test       
;
run;

proc freq data=have noprint;
   tables id* dt* val/out=work.summary(drop=percent);
run;

data temp;
   set work.summary;
   by id;
   ldt=lag(dt);
   if first.id then do;
      runningtot=0;
      prevdt=.;
   end;
   else prevdt= ldt;
   runningtot+count;

   format prevdt mmddyys8.;
   drop ldt;
run;
proc sort data=temp;
   by id descending dt;
run;
data want;
   set temp;
   by id;
   if first.id then delete;
   rename count=volume runningtot=tot_as_of_dt;
run;