I have a dataset like this:
Month Key
1 x
1 y
1 z
2 y
2 z
3 x
3 y
I want a dataset like this:
Month Key Last_occured_before_month
1 x 0
1 y 0
1 z 0
2 y 0
2 z 0
3 x 1
3 y 0
where the in each month, it will look for last occuring value of that key and wherever that month is found it will keep the differenced value.
This delivers exactly what you want:
data have;
length month 3 key $ 1;
input month key;
cards;
1 x
1 y
1 z
2 y
2 z
3 x
3 y
;
run;
proc sort data=have;
by key month;
run;
data want;
set have;
by key;
x1 = lag1(month);
if first.key or x1 = . or (month - x1) = 1
then last_occured_before_month = 0;
else last_occured_before_month = x1;
drop x1;
run;
proc sort data=want;
by month key;
run;
This logic is failing in following scenario:
1 x
1 y
2 y
2 z
3 x
3 y
4 y
4 x
5 z
6 a
7 z
7 y
;
Then you need to post a more complete description (complete want/have data) of your problem. My solution solves your initial post.
1 x 0
1 y 0
2 y 0
2 z 0
3 x 1
3 y 0
4 y 0
4 x 0
5 z 2
6 a 0
7 z 1
7 y 2
At each month, it will look for occurrence of they key previously, so basically count the skipped months
Hello,
data have;
input month key $;
datalines;
1 x
1 y
2 y
2 z
3 x
3 y
4 y
4 x
5 z
6 a
7 z
7 y
;
proc sort data=have;
by key month;
run;
data want;
set have;
by key;
skmonths=dif(month)-1;
if first.key then skmonths=0;
run;
data have; input month key $; datalines; 1 x 1 y 2 y 2 z 3 x 3 y 4 y 4 x 5 z 6 a 7 z 7 y ; run; data want; if _n_ eq 1 then do; if 0 then set have; declare hash h(); h.definekey('key'); h.definedata('month'); h.definedone(); end; set have; dif=0; if h.check()=0 then do; m=month;h.find();dif=m-month-1;month=m;end; h.replace(); drop m; run;
Xia Keshan
Loko's answer seems very simple to me.
But you need re-sort it again . Do you want double sort ?
yea.. double sorting is fine for me..
and a sql version:
data have;
input month key $;
datalines;
1 x
1 y
2 y
2 z
3 x
3 y
4 y
4 x
5 z
6 a
7 z
7 y
RUN;
PROC SQL;
CREATE TABLE WANT AS
SELECT
T1.MONTH,
T1.KEY,
coalesce(Min(T1.MONTH-T2.MONTH),0) as MonthDiff
FROM
HAVE T1
LEFT OUTER JOIN HAVE T2
ON T1.KEY=T2.KEY AND T2.MONTH < T1.MONTH
GROUP BY 1,2;
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.