BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Jeff_DOC
Pyrite | Level 9

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;

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

11 REPLIES 11
art297
Opal | Level 21

What identifies a group? 

 

Art, CEO, AnalystFinder.com

 

Astounding
PROC Star

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.

Shmuel
Garnet | Level 18

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.

Jeff_DOC
Pyrite | Level 9

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.

 

 

art297
Opal | Level 21

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

Shmuel
Garnet | Level 18

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;
Jeff_DOC
Pyrite | Level 9

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;

art297
Opal | Level 21

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

 

Jeff_DOC
Pyrite | Level 9

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;

art297
Opal | Level 21

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

Jeff_DOC
Pyrite | Level 9

My apologies for being new to this.

 

Thanks for the help. It worked.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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