topic Re: A unusual result from creating a lead variable in New SAS User

A unusual result from creating a lead variable

NanZ — Thu, 09 Apr 2020 21:09:26 GMT

Hi SAS users,

I met a very strange situation when creating a lead variable or I had a very low level mistake in my code.

Code and example data are as the following.

After run the code, the lead_datein for time = 2 should be equal to the last datin or 2008-09-15. But the created lead_datein actually is 2008-09-14, which the value comes from nowhere. The lead_dateout is also not right.

Any help is appreciated!

data have;
input clientid datein dateout;
datalines;
1 16519 16523
1 17779 17786
1 17789 17790
1 17790 17794
;

proc sort data= have; by clientid datein dateout; run;

proc expand data= have
out= want;

  convert datein = lead_datein /transformout = (lead 1);
  convert dateout = lead_dateout /transformout = (lead 1);
  by clientid;
run;

data want ;
format clientid time datin datout lead_datin lead_datout ;
set want ;

format lead_datein yymmdd10. lead_dateout yymmdd10. datein yymmdd10. dateout yymmdd10. ;

label lead_datein="lead_datin";
label lead_dateout="lead_datout";

* Change datout and lead_datout (generated from creating the Lead_ variable) back to missing;
*if flag_datout_missing_all = 1 then datout = . ;

if dateout>lead_datein and lead_datein > 0 then flag1 = 1 ;
run;

Re: A unusual result from creating a lead variable

PGStats — Thu, 09 Apr 2020 21:32:43 GMT

I get this out of proc expand, which seems OK to me:

clientid TIME 	lead_datein 	lead_dateout 	datein 	        dateout
1 	 0 	2008-09-04 	2008-09-11 	2005-03-24 	2005-03-28
1 	 1 	2008-09-14 	2008-09-15 	2008-09-04 	2008-09-11
1 	 2 	2008-09-15 	2008-09-19 	2008-09-14 	2008-09-15
1 	 3 	. 	        . 	        2008-09-15 	2008-09-19

Re: A unusual result from creating a lead variable

NanZ — Fri, 10 Apr 2020 02:03:01 GMT

Thanks.

That is really bizarre. I asked two of my colleagues to run the code. One with 64 bit SAS generated the correct result; the other one with 32 bit SAS (the same as mine) generated the result the same as mine.