## Keeping differenced value of month for last occuring key

Regular Contributor
Posts: 196

# Keeping differenced value of month for last occuring key

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.

Super User
Posts: 10,215

## Re: Keeping differenced value of month for last occuring key

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;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Regular Contributor
Posts: 196

## Re: Keeping differenced value of month for last occuring key

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

;

Super User
Posts: 10,215

## Re: Keeping differenced value of month for last occuring key

Then you need to post a more complete description (complete want/have data) of your problem. My solution solves your initial post.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Regular Contributor
Posts: 196

## Re: Keeping differenced value of month for last occuring key

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

Super Contributor
Posts: 319

## Re: Keeping differenced value of month for last occuring key

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;

Super User
Posts: 10,770

## Re: Keeping differenced value of month for last occuring key

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

Regular Contributor
Posts: 196

## Re: Keeping differenced value of month for last occuring key

Loko's answer seems very simple to me.

Super User
Posts: 10,770

## Re: Keeping differenced value of month for last occuring key

But you need re-sort it again . Do you want double sort ?

Regular Contributor
Posts: 196

## Re: Keeping differenced value of month for last occuring key

yea.. double sorting is fine for me..

Super Contributor
Posts: 578

## Re: Keeping differenced value of month for last occuring key

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;

Discussion stats
• 10 replies
• 335 views
• 1 like
• 5 in conversation