multiple set statement for self join

[Steelers_In_DC]

I am misunderstanding using multiple set statements.  Here is a piece of code, it's an idea I have but not getting me what I want:

data D3A;

set complete_sort;

by power_id;

count+1;

if first.power_id then count = 1;

where pass_date <> . and '30aug2012'd <= default_date_end <= '01jul2013'd;

if count > 1;

output;

set complete_sort;

run;

I would like to take count > 1 from one dataset and then join it back to the original dataset and pull all of the records, including where count = 1.  Can you show me how to do that in one step with multiple set statements?

Thank You,

[mohamed_zaki]

Not sure  what do you want to do.

Why not provide sample datasets.

You can check Multiple Set Statements in a Data Step: for good examples.

[Steelers_In_DC]

have

id count

1 1

1 2

1 3

2 1

2 2

3 1

4 1

5 1

want

id count

1 1

1 2

1 3

2 1

2 2

[stat_sas]

May be sql can do it more easily.

proc sql;

create table want as

select * from have

group by id

having count(*)>1

order by id, count;

quit;

[Steelers_In_DC]

I know how to do it in sql, I'm trying to get a better understanding of multiple set statements and haven't any good literature for this example online.

Thanks though,

[Astounding · Accepted solution]

For this approach, you do need two SET statements.  But the structure will change considerably.

data want;

   count=0;

   do until (last.power_id);

      set complete_sort;

      by power_id;
      if pass_date <> . and '30aug2012'd <= default_date_end <= '01jul2013'd then count + 1;

   end;

   do until (last.power_id);

      set complete_sort;

      by power_id;

      if count > 0 then output;

   end;

run;

The top DO loop processes one POWER_ID, coming up with a COUNT value.  Then the bottom DO loop reads the same observations a second time, and outputs (depending on the value of COUNT).

Here are a couple of basic principles that may help.

Each SET statement operates 100% independently of any other SET statement.  Each reads the first observation, then the second, then the third, etc.

When the DATA step contains an OUTPUT statement, there is no automatic output for every observation.  Instead, OUTPUT only takes place where the OUTPUT statement appears.

Good luck.

[Steelers_In_DC]

Awesome, thanks.

[Steelers_In_DC]

I changed the code around a little for the exact result I want, this works great, I appreciate the extra explanation you add.  That was a big help:

data want;

   do until (last.power_id);

      set complete_sort;

      by power_id;

      count+1;

      if first.power_id then count = 1;

   end;

   do until (last.power_id);

      set complete_sort;

      by power_id;

      if count > 1 then output;

   end;

/*         where pass_date <> . and '30aug2012'd <= default_date_end <= '01jul2013'd;*/

run;