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?
Do
test = last.metro;
and look at the resulting dataset. It could be that it is (almost) always true.
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.
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.