topic Re: Keeping differenced value of month for last occuring key in SAS Procedures

Keeping differenced value of month for last occuring key

munitech4u — Mon, 10 Nov 2014 11:36:19 GMT

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.

Re: Keeping differenced value of month for last occuring key

Kurt_Bremser — Mon, 10 Nov 2014 11:59:33 GMT

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;

Re: Keeping differenced value of month for last occuring key

munitech4u — Mon, 10 Nov 2014 12:21:52 GMT

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

;

Re: Keeping differenced value of month for last occuring key

Kurt_Bremser — Mon, 10 Nov 2014 12:25:50 GMT

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

Re: Keeping differenced value of month for last occuring key

munitech4u — Mon, 10 Nov 2014 12:31:59 GMT

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

Re: Keeping differenced value of month for last occuring key

Loko — Mon, 10 Nov 2014 12:48:43 GMT

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;

Re: Keeping differenced value of month for last occuring key

Ksharp — Mon, 10 Nov 2014 13:47:35 GMT


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

Re: Keeping differenced value of month for last occuring key

munitech4u — Mon, 10 Nov 2014 14:03:33 GMT

Loko's answer seems very simple to me.

Re: Keeping differenced value of month for last occuring key

Ksharp — Mon, 10 Nov 2014 14:14:55 GMT

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

Re: Keeping differenced value of month for last occuring key

munitech4u — Mon, 10 Nov 2014 14:26:17 GMT

yea.. double sorting is fine for me..

Re: Keeping differenced value of month for last occuring key

DBailey — Mon, 10 Nov 2014 19:19:40 GMT

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;