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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.