Cannot get it to work with lag involved

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 122
Accepted Solution

Cannot get it to work with lag involved

I have a dataset. I want to replace missing data with lagged ones for each permno. Please see below for what I want to do

before

permno          date          hexcd

10000         2000/1            1

10000         2000/2            1

10000         2000/3            .

10001         2000/1            2

10001         2000/2            2

10001         2000/3            .

10001         2000/4            2

I want to get

permno          date          hexcd

10000         2000/1            1

10000         2000/2            1

10000         2000/3            1

10001         2000/1            2

10001         2000/2            2

10001         2000/3            2

10001         2000/4            2

I use the following code, and the dataset is already sorted by permno date

data crsp; set crsp;

  by permno date;

  if hexcd = . then hexcd=lag(hexcd);

  retain hexcd;

run;

I also tried

data crsp; set crsp;

  by permno date;

  if hexcd = . and lag(hexcd) ^= . then hexcd=lag(hexcd);

  retain hexcd;

run;

I run the above code, but nothing happened. I don't know what is the problem. Please help. Thanks.


Accepted Solutions
Solution
‎05-29-2014 05:08 PM
Super Contributor
Posts: 275

Re: Cannot get it to work with lag involved

Assume that there are no missing value in the first month of each permno.

data have;

input permno     date : $          hexcd;

cards;

10000         2000/1            1

10000         2000/2            1

10000         2000/3            .

10001         2000/1            2

10001         2000/2            2

10001         2000/3            .

10001         2000/4            2

10002         2000/1            2

10002         2000/2            3

10002         2000/3            .

10002         2000/4            .

;

run;

data want;

set have;

by permno ;

retain temp;

if hexcd^=. then temp=hexcd;

else hexcd=temp;

if last.permno then call missing(temp);

drop temp;

run;

View solution in original post


All Replies
Solution
‎05-29-2014 05:08 PM
Super Contributor
Posts: 275

Re: Cannot get it to work with lag involved

Assume that there are no missing value in the first month of each permno.

data have;

input permno     date : $          hexcd;

cards;

10000         2000/1            1

10000         2000/2            1

10000         2000/3            .

10001         2000/1            2

10001         2000/2            2

10001         2000/3            .

10001         2000/4            2

10002         2000/1            2

10002         2000/2            3

10002         2000/3            .

10002         2000/4            .

;

run;

data want;

set have;

by permno ;

retain temp;

if hexcd^=. then temp=hexcd;

else hexcd=temp;

if last.permno then call missing(temp);

drop temp;

run;

Trusted Advisor
Posts: 1,204

Re: Cannot get it to work with lag involved

data want(drop=_x);

set have;

by permno;

_x = lag(hexcd);

if missing(hexcd) then hexcd = _x;

run;

Frequent Contributor
Posts: 122

Re: Cannot get it to work with lag involved

This will not solve the problem that there are continuous missing hexcd for each permno. Hexcd will be reset to the correct one for the very first missing hexcd, but all the left are left as missing.

Contributor
Posts: 22

Re: Cannot get it to work with lag involved

Haven't tried but...

data want(drop=lasthex);

set have;

by permno;

if hexcd = . then do;

     retain lasthex;

     lasthex = lag(hexcd);     /* that should get you past the problem SeanZ pointed out where there are consecutive records missing values for hexcd     */

end;

run;

Super User
Posts: 9,682

Re: Cannot get it to work with lag involved

data x;
input permno          date  $         hexcd ;
cards;
10000         2000/1            1
10000         2000/2            1
10000         2000/3            .
10001         2000/1            2
10001         2000/2            2
10001         2000/3            .
10001         2000/4            2
;
run;
data x;
 set x;
 by permno;
 retain new .;
 if first.permno then call missing(new);
  if not missing(hexcd) then new=hexcd;
  drop hexcd;
run;

Xia Keshan

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 214 views
  • 3 likes
  • 5 in conversation