BookmarkSubscribeRSS Feed
munitech4u
Quartz | Level 8

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.

10 REPLIES 10
Kurt_Bremser
Super User

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;

munitech4u
Quartz | Level 8

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

;

munitech4u
Quartz | Level 8

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

Loko
Barite | Level 11

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;

Ksharp
Super User

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

munitech4u
Quartz | Level 8

Loko's answer seems very simple to me.

Ksharp
Super User

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

munitech4u
Quartz | Level 8

yea.. double sorting is fine for me..

DBailey
Lapis Lazuli | Level 10

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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