First. Last. By

[MikeFranz]

Hi, I have the code below, where I sort my dataset by my variables, and then use a data step to split my data into by groups, and create a count, resetting on the by group.

Please see the code below:

PROC SORT DATA = kpi.occ_rent out = occ_rent;
	by property_id date company0 metro;
RUN;
DATA ave_vac_by_pm_market;
	set occ_rent (rename = (company0 = company));
	by property_id date company metro;
	/* Increment the vacancy days by one for each day vacant */
	if active = 1 then
		vacancy_days + 1;
	if active = 0 or occupied0 = 1 then
		vacancy_days = 0;
/* 	if last.metro then */
/* 		vacancy_days = 0; */
RUN;

The above code, with the last two steps commented out, works just fine, except it doesn't split my count up every time there is a new "metro". However, if I add the final two steps in, all my "vacancy_days" read as zero. Its almost as though it thinks that each row is a new "metro", even though this isn't the case.

Any thoughts on why this may be?

[Kurt_Bremser]

Do

test = last.metro;

and look at the resulting dataset. It could be that it is (almost) always true.

[MikeFranz]

Thanks for the reply.
Yes, it is coming up as true (i.e. 1) in each row. But looking at the dataset I can clearly see that it is not true. The data are sorted, as per the sort step above. Any idea why it is always coming up as true?

[Kurt_Bremser]

You should change to a first. logic for the reset of the counter. This is much easier to comprehend for someone who reads the code.

[WarrenKuhfeld]

You do know that last.last-variable is true not when the last variable is on the last observation but when the BY group is on the last observation.  In this contrived example, last.x3 is always true because earlier values change frequently.

data x;
   do i = 1 to 10;
      x1 = ceil(12 * uniform(7));
      x2 = ceil(12 * uniform(7));
      x3 = ceil( 2 * uniform(7));
      output;
   end;
run;

proc sort out=x2;
   by x1-x3;
run;
   
data x3;
   set x2;
   by x1-x3;
   last = last.x3;
run;
   
proc print; run;

Obs     i    x1    x2    x3    last
                                   
  1     6     1    11     2      1 
  2    10     2     1     2      1 
  3     1     4    10     2      1 
  4     9     4    11     1      1 
  5     4     6     9     2      1 
  6     2    10     6     2      1 
  7     3    10     9     1      1 
  8     7    11     8     2      1 
  9     8    11    11     1      1 
 10     5    12     6     2      1 

[Astounding]

Logically, you may need to insert an OUTPUT statement before those last two lines. 

As it stands now, you are changing VACANCY_DAYS to 0 before outputting the final observation in each BY group.  That obliterates many of the totals you are accumulating.  Output first, then set VACANCY_DAYS to 0.