if statement in many to many merge

Solved
Super Contributor
Posts: 285

if statement in many to many merge

Hello,

In this program, I wonder why IF statement does not work. I have inherited programs like this ie. using IF statement after OUTPUT statement.

```data a;
input a;
cards;
1
2
3
;
run;

data b;
input b;
cards;
4
5
6
;
run;

data want;
set a;
do i = 1 to nobs;
set b point=i nobs = nobs;
output;
end;
if b > 5;
run;```

Accepted Solutions
Solution
‎10-22-2016 02:21 PM
Super User
Posts: 23,778

Re: if statement in many to many merge

Your example is a subsetting IF, any IF that operated on a variable that was retained from obs to obs would be affected.

This is a useless example, but I know for a fact that I've used this to increment a value after the OUTPUT statement on purpose to retain a value across rows. There are multiple ways to do this and I can't come up with a good example but it has it's uses. Note that in the example below the effective rate in the log is different than the effective rate on the data set.

``````data have;
do i=1 to 100;
rate=0.05; deposit=100;
output;
end;
run;

data want;
set have;
retain effective_rate 0.03 x 0;

x=deposit+x*(1+effective_rate);

output;

effective_rate =(rate**_n_);

put effective_rate;
run;``````

All Replies
Super User
Posts: 10,283

Re: if statement in many to many merge

This subsetting if is completely useless. The explicit output statement in the loop deactivates the implicit output that usually happens at the conclusion of a data step iteration.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Posts: 1,837

Re: if statement in many to many merge

Did you mean to do:

```data want;
set a;
do i = 1 to nobs;
set b point=i nobs = nobs;
if b > 5 then output;
end;

run;```

Super Contributor
Posts: 285

Re: if statement in many to many merge

@Shmuel This is how it should be done. As I mentioned earlier, I have some programs that have used subsetting IF after explicit OUTPUT statement that confused me.

Super User
Posts: 23,778

Re: if statement in many to many merge

@SAS_inquisitive In this particular case it's useless. We can't say that about all cases.

Super Contributor
Posts: 285

Re: if statement in many to many merge

@Reeza I wonder if you have any example.

Solution
‎10-22-2016 02:21 PM
Super User
Posts: 23,778

Re: if statement in many to many merge

Your example is a subsetting IF, any IF that operated on a variable that was retained from obs to obs would be affected.

This is a useless example, but I know for a fact that I've used this to increment a value after the OUTPUT statement on purpose to retain a value across rows. There are multiple ways to do this and I can't come up with a good example but it has it's uses. Note that in the example below the effective rate in the log is different than the effective rate on the data set.

``````data have;
do i=1 to 100;
rate=0.05; deposit=100;
output;
end;
run;

data want;
set have;
retain effective_rate 0.03 x 0;

x=deposit+x*(1+effective_rate);

output;

effective_rate =(rate**_n_);

put effective_rate;
run;``````

Posts: 1,837

Re: if statement in many to many merge

@SAS_inquisitive - using statement like  "IF <condition> ; " intends to filter implicit output;

In your code, the explicit output was already done and the IF statement has no effect.

You wrote "...why IF statement does not work." - what was your target when adding this IF statement ?

If your target was to filter output then your code should be:

IF B > 5 then output;

In @Reeza's example, the statment following OUTPUT is to deal with a retained variable, that may affect next itteration.

☑ This topic is solved.