multiple set statement for self join

Accepted Solution Solved
Reply
Valued Guide
Posts: 858
Accepted Solution

multiple set statement for self join

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,


Accepted Solutions
Solution
‎01-08-2015 01:30 PM
Super User
Posts: 5,096

Re: multiple set statement for self join

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.

View solution in original post


All Replies
Super Contributor
Posts: 490

Re: multiple set statement for self join

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.

Valued Guide
Posts: 858

Re: multiple set statement for self join

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

Trusted Advisor
Posts: 1,204

Re: multiple set statement for self join

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;

Valued Guide
Posts: 858

Re: multiple set statement for self join

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,

Solution
‎01-08-2015 01:30 PM
Super User
Posts: 5,096

Re: multiple set statement for self join

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.

Valued Guide
Posts: 858

Re: multiple set statement for self join

Awesome, thanks.

Valued Guide
Posts: 858

Re: multiple set statement for self join

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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 537 views
  • 4 likes
  • 4 in conversation