DATA Step, Macro, Functions and more

if statement in many to many merge

Accepted Solution Solved
Reply
Super Contributor
Posts: 271
Accepted Solution

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: 19,822

Re: if statement in many to many merge

Posted in reply to SAS_inquisitive

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;

 

 

View solution in original post


All Replies
Super User
Posts: 7,811

Re: if statement in many to many merge

Posted in reply to SAS_inquisitive

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
Trusted Advisor
Posts: 1,572

Re: if statement in many to many merge

Posted in reply to SAS_inquisitive

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: 271

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: 19,822

Re: if statement in many to many merge

Posted in reply to SAS_inquisitive

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

Super Contributor
Posts: 271

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: 19,822

Re: if statement in many to many merge

Posted in reply to SAS_inquisitive

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;

 

 

Trusted Advisor
Posts: 1,572

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.

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

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