Hello.
I am having difficulty recoding the first observation in a group. Basically I need it to recode the occupied variable to a Y when both conditions are met. What I'm getting is all variables recoded to Y where the first condition is met (occupied = 'N').
Please see the attachment.
Thank you very much for any help out there.
data op23a_04;
set op23a_03;
by locacde number count_date occupied;
if (occupied = 'N' and first.occupied = 1) then do;
occupied = 'Y';
end;
else do;
end;
run;
I think that the following will do what you want to achieve:
data op23a_04; set op23a_03; by location unit room record notsorted; if (occupied = 'N' and missing(record) and first.record) then do; deletion_flag = 'Y'; end; run;
Art, CEO, AnalystFinder.com
What identifies a group?
Art, CEO, AnalystFinder.com
Since OCCUPIED is a BY variable, it must be part of the incoming data set. So how do you know that it changed at all? Maybe it always was Y and just remained Y.
Since coumt_date is changing in each row and is part of BY statement,
then each row is defined as a group.
What you are looking for seems that you need to drop count_date from the BY saement.
Ah hah! Now that makes all kind of sense. No wonder each value of occupied changes.
Without count date though how would I keep them in order to be sure only the first occurrence, in date order, of occupied is recoded? If I don't order them by group and place it in the BY statement wouldn't they just be randomly ordered and I'd be selecting some random observation and recoding it?
Sorry, I'm more than a bit new at this.
When you use a by statement in a datastep SAS is expecting that the data had been sorted in the order shown in the by statement.
In your case if locacde is the variable that identifies group, and the data have already been sorted in the order shown in your previous by statement, all you would need is:
data op23a_04; set op23a_03; by locacde; if (occupied = 'N' and first.locacde = 1) then occupied = 'Y'; run;
Art, CEO, AnalystFinder.com
You can keep the data sorted as you want but define the group without the count_date
like in:
proc sort data=have;
by locacde number count_date occupied;
run;
data op23a_04;
set op23a_03;
by locacde number;
if (occupied = 'N' and first.number = 1) then occupied = 'Y';
run;
Sorry but I'm still having issues here. I'll attach a new file as an example. What I need is to flag the first occurrence of an unoccupied location if that unoccupied location is the first unoccupied record in that room. What I keep getting is either flagging nothing anywhere or flagging each unoccupied location (all OCCUPIED = N) across all the data regardless of if it's the first occurrence or not.
If you look to row 97 and 98, it would be row 97 I would want flagged as removable but not row 98 since that is the second occurrence of the same room flagged as unoccupied. It was unoccupied for two days but we only want to remove the first day. Row 103 should also be flagged since it is the first occurrence of E1-11B where it shows not occupied. Row 275 should also be flagged but not row 276 since that is the second occurrence of E2-01B.
Here's the current version of my step (that doesn't work).
I would sure appreciate any help. Sorry about being a bit less than clear before.
data op23a_04;
set op23a_03;
by location unit room;
if (occupied = 'N' and first.occupied = 1) then do;
deletion_flag = 'Y';
end;
run;
I think that the following will do what you want to achieve:
data op23a_04; set op23a_03; by location unit room record notsorted; if (occupied = 'N' and missing(record) and first.record) then do; deletion_flag = 'Y'; end; run;
Art, CEO, AnalystFinder.com
I so wish I could tell you that worked....but I don't get anything different. All I get is a new variable called "deletion_flag" (which I should) but no values across the entire variable. The entire column is blank.
This is a tough one.
data op23a_04;
set op23a_03;
by location unit room notsorted;
if (occupied = 'N' and missing(record) and first.record) then deletion_flag = 'Y';
run;
That is NOT the code I suggested. If you're going to deviate from what is suggested then, of course, it might not work!
I imported your workbook and the code worked perfectly for me.
Art, CEO, AnalystFinder.com
My apologies for being new to this.
Thanks for the help. It worked.
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.