DATA Step, Macro, Functions and more

First. Last. By

Reply
Contributor
Posts: 44

First. Last. By

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?

 

 

 

Super User
Posts: 9,580

Re: First. Last. By

Posted in reply to MikeFranz

Do

test = last.metro;

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 44

Re: First. Last. By

Posted in reply to KurtBremser
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?
Super User
Posts: 9,580

Re: First. Last. By

Posted in reply to MikeFranz

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
SAS Super FREQ
Posts: 496

Re: First. Last. By

Posted in reply to MikeFranz

 

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 

 

Super User
Posts: 6,541

Re: First. Last. By

Posted in reply to MikeFranz

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.

Ask a Question
Discussion stats
  • 5 replies
  • 137 views
  • 0 likes
  • 4 in conversation