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

I would like to find an easier solution to a problem I am having. I can manage this task, however it's though several steps outside SAS (SQL LIMIT). At best I don't get all the observations correctly in SAS alone.

I have used a POINT and SET however I can't get the BY variables working.

Would be good to have a data step or PROC SQL to be able to achieve this in SAS alone.

The code I have modified is below. Currently, it does not discriminate between the ID, it just follows the order of the set.

data WANT;
set HAVE  nobs=nobs;

if FLAG = 'Y' then do;
  current = _N_;
  prev = current - 1;
  next = current + 1;

if prev > 0 then do;
    set HAVE point = prev;
      POSTION = 'PREVIOUS';
      output;
   end;

set HAVE point = current;
     POSTION = 'CURRENT';
       output;

if next <= nobs then do;
   set HAVE point = next;
    POSTION = 'NEXT';
    output;
  end;


end;

run;

 

I have a table with multiple ID's. Within each ID there is an observation with a flag.

OBSIDFLAG
1RED
2RED
3REDY
4RED
5RED
6BLUEY
7BLUE
8GREEN
9GREENY
10PURPLE
11PURPLE
12PURPLE
13PURPLEY
14PURPLE
15PURPLE

I would like to locate and tag the current, previous and next observation based on OBS order (or dates) and the ID as sorted above.

The output would look like below.

OBSIDFLAGPOSTION
1RED
2REDPREVIOUS
3REDYCURRENT
4REDNEXT
5RED
6BLUEYCURRENT
7BLUENEXT
8GREENPREVIOUS
9GREENYCURRENT
10PURPLE
11PURPLE
12PURPLEPREVIOUS
13PURPLEYCURRENT
14PURPLENEXT
15PURPLE

Any help would be greatly appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

/* dertermine the succeeding records, using lag() function and original order */

data int1 (drop=oldflg oldid);

set have;

length POSTION $ 8;

count = _N_;

oldflg = lag(flag);

oldid = lag(id);

if oldflg = 'Y' and oldid = id then POSTION = 'NEXT';

if flag = 'Y' then POSTION = 'CURRENT';

run;

/* turn the dataset upside down */

proc sort data=int1;

by descending count;

run;

/* dertermine the preceding records, using the reverse order */

data int2 (drop=oldflg oldid);

set int1;

oldflg = lag(flag);

oldid = lag(id);

if oldflg = 'Y' and oldid = id then POSTION = 'PREVIOUS';

run;

/* establish original order */

proc sort data=int2 out=want (drop=count);

by count;

run;

View solution in original post

7 REPLIES 7
Kurt_Bremser
Super User

/* dertermine the succeeding records, using lag() function and original order */

data int1 (drop=oldflg oldid);

set have;

length POSTION $ 8;

count = _N_;

oldflg = lag(flag);

oldid = lag(id);

if oldflg = 'Y' and oldid = id then POSTION = 'NEXT';

if flag = 'Y' then POSTION = 'CURRENT';

run;

/* turn the dataset upside down */

proc sort data=int1;

by descending count;

run;

/* dertermine the preceding records, using the reverse order */

data int2 (drop=oldflg oldid);

set int1;

oldflg = lag(flag);

oldid = lag(id);

if oldflg = 'Y' and oldid = id then POSTION = 'PREVIOUS';

run;

/* establish original order */

proc sort data=int2 out=want (drop=count);

by count;

run;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

As an alternative, an SQL based approach:

proc sql;

  create table WANT as

  select  A.*,

          case when A.OBS=B.OBS-1 then "Previous"

               when A.OBS=B.OBS then "Current"

               when A.OBS=B.OBS+1 then "Next"

               else "" end as POSITION

  from    WORK.HAVE A

  left join (select * from WORK.HAVE where FLAG="Y") B

  on      A.ID=B.ID

  order by A.OBS;

quit;

Tucky
Obsidian | Level 7

Thank you Kurt and RW9, both awesome answers.

Nice work.

Tom
Super User Tom
Super User

You can do it just using FIRST. and LAST. flags.  At least for your sample data.

data want ;

  set have;

  by color flag notsorted ;

  if last.flag and flag=' ' and not last.color then position='PREVIOUS';

  else if flag='Y' then position='CURRENT';

  else if first.flag and flag=' ' and not first.color then position='NEXT';

run;

Haikuo
Onyx | Level 15

Elegant! I know it goes without saying with all Tom's code, but I just couldn't help with this one! Thank you Tom !

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Yes, interesting idea Tom, would not have thought about using flag as a grouping variable with First/Last.

Ksharp
Super User
data have;
input OBS ID $ FLAG $;
cards;
1 RED .   
2 RED  .
3 RED Y 
4 RED  .
5 RED   .
6 BLUE Y 
7 BLUE  .
8 GREEN .
9 GREEN Y 
10 PURPLE   .
11 PURPLE   .
12 PURPLE  .
13 PURPLE Y .
14 PURPLE  .
15 PURPLE  .
;
run;
data want;
 merge have have(firstobs=2 keep=id flag rename=(id=_id flag=_flag)); 
 length POSTION $ 20;
 lag_flag=lag(flag);lag_id=lag(id);
 if FLAG='Y' then POSTION='CURRENT';
  else if missing(FLAG) and _FLAG='Y' and id=_id then POSTION='PREVIOUS';
   else if missing(FLAG) and lag_flag='Y' and id=lag_id then POSTION='NEXT';
drop lag_: _:;
run;

Xia Keshan

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
  • 7 replies
  • 3306 views
  • 11 likes
  • 6 in conversation