BookmarkSubscribeRSS Feed
MikeFranz
Quartz | Level 8

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?

 

 

 

5 REPLIES 5
MikeFranz
Quartz | Level 8
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?
WarrenKuhfeld
Rhodochrosite | Level 12

 

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
PROC Star

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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 5 replies
  • 823 views
  • 0 likes
  • 4 in conversation