topic Re: multiple set statement for self join in SAS Programming

multiple set statement for self join

Steelers_In_DC — Thu, 08 Jan 2015 17:54:07 GMT

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,

Re: multiple set statement for self join

mohamed_zaki — Thu, 08 Jan 2015 18:10:27 GMT

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.

Re: multiple set statement for self join

Steelers_In_DC — Thu, 08 Jan 2015 18:18:40 GMT

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

Re: multiple set statement for self join

stat_sas — Thu, 08 Jan 2015 18:28:03 GMT

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;

Re: multiple set statement for self join

Astounding — Thu, 08 Jan 2015 18:30:42 GMT

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.

Re: multiple set statement for self join

Steelers_In_DC — Thu, 08 Jan 2015 18:31:31 GMT

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,

Re: multiple set statement for self join

Steelers_In_DC — Thu, 08 Jan 2015 18:32:08 GMT

Awesome, thanks.

Re: multiple set statement for self join

Steelers_In_DC — Thu, 08 Jan 2015 19:18:13 GMT

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;