Help using Base SAS procedures

Keeping differenced value of month for last occuring key

Reply
Regular Contributor
Posts: 187

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.

Esteemed Advisor
Posts: 6,256

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
Regular Contributor
Posts: 187

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

;

Esteemed Advisor
Posts: 6,256

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
Regular Contributor
Posts: 187

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: 305

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;

Grand Advisor
Posts: 9,444

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: 187

Re: Keeping differenced value of month for last occuring key

Loko's answer seems very simple to me.

Grand Advisor
Posts: 9,444

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: 187

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;

Ask a Question
Discussion stats
  • 10 replies
  • 253 views
  • 1 like
  • 5 in conversation