topic Re: how to compare values back in time to identify the sequence of change in SAS Programming

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

havmaage — Tue, 19 May 2020 08:26:16 GMT

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

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

ed_sas_member — Tue, 19 May 2020 08:50:31 GMT

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;

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

havmaage — Tue, 19 May 2020 09:00:26 GMT

Yes thats correct, sorry for my bad example

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

ed_sas_member — Tue, 19 May 2020 09:10:29 GMT

OK no worries 😊

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

Best,

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

havmaage — Tue, 19 May 2020 09:38:06 GMT

Thank you so much this is perfect

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

ed_sas_member — Tue, 19 May 2020 09:39:05 GMT

You're welcome @havmaage !