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
OK no worries 😊
Let me know if the code above does meet your needs.
Best,
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.
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;
OK no worries 😊
Let me know if the code above does meet your needs.
Best,
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: