topic Re: Recode First Observations in a Group in SAS Programming

Recode First Observations in a Group

Jeff_DOC — Thu, 19 Oct 2017 00:14:32 GMT

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;

Re: Recode First Observations in a Group

art297 — Thu, 19 Oct 2017 01:39:26 GMT

What identifies a group?

Art, CEO, AnalystFinder.com

Re: Recode First Observations in a Group

Astounding — Thu, 19 Oct 2017 03:09:14 GMT

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.

Re: Recode First Observations in a Group

Shmuel — Thu, 19 Oct 2017 04:45:56 GMT

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.

Re: Recode First Observations in a Group

Jeff_DOC — Thu, 19 Oct 2017 15:19:20 GMT

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.

Re: Recode First Observations in a Group

art297 — Thu, 19 Oct 2017 16:28:05 GMT

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

Re: Recode First Observations in a Group

Shmuel — Fri, 20 Oct 2017 00:00:29 GMT

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;

Re: Recode First Observations in a Group

Jeff_DOC — Mon, 23 Oct 2017 22:12:24 GMT

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;

Re: Recode First Observations in a Group

art297 — Mon, 23 Oct 2017 22:44:52 GMT

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

Re: Recode First Observations in a Group

Jeff_DOC — Mon, 23 Oct 2017 23:07:46 GMT

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;

Re: Recode First Observations in a Group

art297 — Mon, 23 Oct 2017 23:42:03 GMT

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

Re: Recode First Observations in a Group

Jeff_DOC — Tue, 24 Oct 2017 00:02:32 GMT

My apologies for being new to this.

Thanks for the help. It worked.