Recode First Observations in a Group

Accepted Solution Solved
Reply
Contributor
Posts: 56
Accepted Solution

Recode First Observations in a Group

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;


Accepted Solutions
Solution
‎10-23-2017 08:02 PM
PROC Star
Posts: 8,149

Re: Recode First Observations in a Group

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

 

View solution in original post


All Replies
PROC Star
Posts: 8,149

Re: Recode First Observations in a Group

What identifies a group? 

 

Art, CEO, AnalystFinder.com

 

Super User
Posts: 6,632

Re: Recode First Observations in a Group

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.

Trusted Advisor
Posts: 1,831

Re: Recode First Observations in a Group

Posted in reply to Astounding

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.

Contributor
Posts: 56

Re: Recode First Observations in a Group

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.

 

 

PROC Star
Posts: 8,149

Re: Recode First Observations in a Group

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

Trusted Advisor
Posts: 1,831

Re: Recode First Observations in a Group

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;
Contributor
Posts: 56

Re: Recode First Observations in a Group

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;

Solution
‎10-23-2017 08:02 PM
PROC Star
Posts: 8,149

Re: Recode First Observations in a Group

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

 

Contributor
Posts: 56

Re: Recode First Observations in a Group

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;

PROC Star
Posts: 8,149

Re: Recode First Observations in a Group

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

Contributor
Posts: 56

Re: Recode First Observations in a Group

My apologies for being new to this.

 

Thanks for the help. It worked.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 204 views
  • 0 likes
  • 4 in conversation