how to compare values back in time to identify the sequence of change

[havmaage]

Hi, 

I am trying to create a datastep thats compares values some dates back in time 

what i am looking for is to identify which name where the amount changes every day and which only change every 5 or 7 days 

my data are like this 

name , date, amount 

ann, 2020-05-11, 1200

ann, 2020-05-12, 1200 

ann, 2020-05-13, 1300

pete, 2020-05-11, 1200

pete, 2020-05-12, 1200

pete, 2020-05-13, 1200

simon, 2020-05-11, 200

simon, 2020-05-12, 1200

simon, 2020-05-13, 1200

Hope someone an help 

[ed_sas_member]

Hi @havmaage 

You datalines don't contain such cases...

Assuming we have the following ones:

- Ann has changes in amount every days.

- Simon has changes in amount every 5 or 7 days <=> has no change in amount for at least 4 consecutive days.

Is that correct?

Do we assume there is always one record per day for each name?

Thank you for the clarifications.

If it is correct, you can try this:

data have;
	infile datalines dlm=",";
	input name $ date:YYMMDD10. amount;
	format date YYMMDD10.;
	datalines;
ann, 2020-05-11, 1000
ann, 2020-05-12, 1200 
ann, 2020-05-13, 1300
pete, 2020-05-11, 1200
pete, 2020-05-12, 1200
pete, 2020-05-13, 1200
simon, 2020-05-11, 200
simon, 2020-05-12, 1200
simon, 2020-05-13, 1200
simon, 2020-05-14, 1200
simon, 2020-05-15, 1200
simon, 2020-05-16, 200
simon, 2020-05-17, 400
simon, 2020-05-18, 1200
	;
run;

/* Count consecutive amounts */

data have2;
	set have;
	by name date amount;
	_lag = lag(amount);
	if first.name then consec = 0;
	if amount ne _lag then consec+1;
	drop _lag;
run;

proc freq data=have2 noprint;
	table name*consec / out=have_freq (drop=consec percent);
run;

/*** Names where the amount changes
 	 every day
	 (Assuming there is one record per day for each name) */

proc sql;
	select name
	from have2
	group by name
	having count(distinct amount) = count(distinct date);
quit;

/*** Names where the amount
	 changes every 5 or 7 days 
	 <=> does not change for at least 4 days
	 (Assuming there is one record per day for each name) */

proc sql;
	select name
	from have_freq
	group by name
	having max(count) >=4;
quit;

[havmaage]

Yes thats correct, sorry for my bad example

[ed_sas_member · Accepted solution]

OK no worries 😊

Let me know if the code above does meet your needs.

Best,

[havmaage]

Thank you so much this is perfect

[ed_sas_member]

You're welcome @havmaage !