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

Hi All,

I have a programing question and would appreciate some suggestions.

I have a data set which has 6 variables: A1, A2, A3, A4, rank and flag.

A1,A2,A3,A4 is by group and within that by group I want to delete flag=1 but its not that simple, I want to delete only those flags which leaves me with consecutive rank variable.

For eg:

A1   A2  A3  A4  Rank  Flag

1      1      1     1     1            0

1      1      1     1     2            1                           -by Group1

1      1      1     1     3            0

1      1      1     1     4            1

1      1      1     2     1            1

1      1      1     2     2            1

1      1      1     2     3            0

1      1      1     2     4            1                       -> by Group2

1      1      1     2     5            1

1      1      1     2     6            0

1      1      1     2     7            1

Result I want:

A1   A2  A3  A4  Rank Flag

1      1      1     1     1            0

1      1      1     1     2            1                           -> by Group1

1      1      1     1     3            0

1      1      1     2     3            0

1      1      1     2     4            1                       -> by Group2

1      1       1    2      5           1

1      1      1     2     6            0

Here important thing is not to break the sequence of rank
variable. I cant have rank 3,6 only in group 2

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

Typically you would need more than one pass, here is an example using 2XDOW:

data have;

     input A1   A2 A3  A4  Rank Flag;

     cards;

1      1 1     1     1            0

1      1 1     1     2            1

1      1 1     1     3            0

1      1 1     1     4            1

1      1 1     2     1            1

1      1 1     2     2            1

1      1 1     2     3            0

1      1 1     2     4            1

1      1 1     2     5            1

1      1 1     2     6            0

1      1      1 2     7            1

;;;;

run;

data want;

     do until (last.flag);

           set have;

           by a4 flag notsorted;

           if (first.a4 or last.a4) and  flag then

                _d=1;

     end;

     do until (last.flag);

           set have;

           by a4 flag notsorted;

           if missing(_d) then

                output;

     end;

run;


Regards,

Haikuo

View solution in original post

4 REPLIES 4
Haikuo
Onyx | Level 15

Typically you would need more than one pass, here is an example using 2XDOW:

data have;

     input A1   A2 A3  A4  Rank Flag;

     cards;

1      1 1     1     1            0

1      1 1     1     2            1

1      1 1     1     3            0

1      1 1     1     4            1

1      1 1     2     1            1

1      1 1     2     2            1

1      1 1     2     3            0

1      1 1     2     4            1

1      1 1     2     5            1

1      1 1     2     6            0

1      1      1 2     7            1

;;;;

run;

data want;

     do until (last.flag);

           set have;

           by a4 flag notsorted;

           if (first.a4 or last.a4) and  flag then

                _d=1;

     end;

     do until (last.flag);

           set have;

           by a4 flag notsorted;

           if missing(_d) then

                output;

     end;

run;


Regards,

Haikuo

Haikuo
Onyx | Level 15

@anna_nag,

  DOW loop is an classic data step implementation that is both powerful and convenient to use. Recently the Hash() and Proc SQL have stolen some thunders from DOW, but as you can see, it still shines here. However, if you have never used or heard of it, it would be very hard for me to explain it in a format of short post. The concept of DOW touches the heart of data step. Here are some references that you may want to take a look:

http://support.sas.com/resources/papers/proceedings12/156-2012.pdf

http://www.pharmasug.org/proceedings/2012/AD/PharmaSUG-2012-AD29.pdf

Another key of my code is to use option: "notsorted". This is to take your current data structure as is, then group it using "by variables". Please also do a search to learn more.

Regards,

Haikuo

Ksharp
Super User
data have;
input A1   A2  A3  A4  Rank  Flag ;
cards;
1      1      1     1     1            0
1      1      1     1     2            1         
1      1      1     1     3            0
1      1      1     1     4            1
1      1      1     2     1            1
1      1      1     2     2            1
1      1      1     2     3            0
1      1      1     2     4            1       
1      1      1     2     5            1
1      1      1     2     6            0
1      1      1     2     7            1
;
run;
data have;
 set have;
 by a1 a2 a3 a4;
 if first.a4 or flag ne lag(flag) then n+1;
run;
proc sql;
 create table want as
  select * from have 
   except
  select * from have
   group by a1,a2,a3,a4 
    having (n eq max(n) and flag=1) or (n eq min(n) and flag=1);
quit; 

Xia Keshan

Anna_nag
Obsidian | Level 7

Thanks..it works!!

sas-innovate-2024.png

Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.

 

Plus, 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
  • 4 replies
  • 1146 views
  • 9 likes
  • 3 in conversation