DATA Step, Macro, Functions and more

Multiple if then / else then statement in one argument

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 110
Accepted Solution

Multiple if then / else then statement in one argument

[ Edited ]

I would like to use shrout whenever available. If shrout is missing, then use shrout1. Lastly, if shrout and shrout 1 are both missing then use shrout2. 

In table form, I am trying to get the following data:

sharesshroutshrout1shrout2WANTMETHOD USED
262324095359850997509970000.000489428878shares / (shrout*1000*1000)
1062363010097.161407430.001052157076shares / (shrout*1000*1000)
12985791..676591600.00019193shares / (shrout2*1000)
3653140.21212212129480.00017222shares / (shrout1*1000*1000)
5856168.41221.0.000142068shares / (shrout1*1000*1000)
5856168....None

 

I wrote the following simple code, but I am missing out shrout2 in this statement. I am not sure how to add another else statement:

data work.want;
	set work.sample;
	if shrout^=. then share_rate=shares/(shrout*1000); else share_rate=shares/(shrout1*1000*1000);
run;

 

Here is the data:

data work.sample;
 infile cards expandtabs truncover;
input 	shares	shrout	shrout1	shrout2;
cards;
26232409	53598	50997	50997000
10623630	10097		.	16140743
12985791		.		.	67659160
3653140			.	21212	21212948
5856168			.	41221	.
5856168			.		.	.
;
run;


Thanks for any suggestions. 


Accepted Solutions
Solution
‎06-01-2017 07:02 PM
PROC Star
Posts: 325

Re: Multiple if then / else then statement in one argument

[ Edited ]

May be something like this

proc sql;

select shares, case when coalesce(shrout,shrout1,shrout2) ^=. then

shares/(coalesce(shrout,shrout1,shrout2)*1000)

else .

end as share_rates from sample;

quit;

 

Modified reply as per reauirement and corrected by @Yegen 

 

proc sql;
create table work.want as
select shares, case when coalesce(shrout,shrout1) ^=. then
shares/(coalesce(shrout,shrout1)*1000*1000)
else shares/(shrout2*1000)
end as share_rates from work.sample;
quit;

 

 

View solution in original post


All Replies
Frequent Contributor
Posts: 110

Re: Multiple if then / else then statement in one argument

I have also tried the following but it didn't work:

data work.want;
	set work.sample;
	if shrout^=. then share_rate=shares/(shrout*1000); else share_rate=shares/(shrout1*1000*1000); 
	else share_rate=shares/(shrout2*1000);
run;
Solution
‎06-01-2017 07:02 PM
PROC Star
Posts: 325

Re: Multiple if then / else then statement in one argument

[ Edited ]

May be something like this

proc sql;

select shares, case when coalesce(shrout,shrout1,shrout2) ^=. then

shares/(coalesce(shrout,shrout1,shrout2)*1000)

else .

end as share_rates from sample;

quit;

 

Modified reply as per reauirement and corrected by @Yegen 

 

proc sql;
create table work.want as
select shares, case when coalesce(shrout,shrout1) ^=. then
shares/(coalesce(shrout,shrout1)*1000*1000)
else shares/(shrout2*1000)
end as share_rates from work.sample;
quit;

 

 

Frequent Contributor
Posts: 110

Re: Multiple if then / else then statement in one argument

@kiranv_ thanks for your reply. Your code does not produce the correct output. I see what you are trying to do, but the formulas used are incorrectly specified. Here is a summary of which method needs to be used when:

IfThen
Shrout ^= .shares / (shrout*1000*1000)
Shrout = . And Shrout1^=.shares / (shrout1*1000*1000)
Shrout = . And Shrout1=.shares / (shrout2*1000)
Frequent Contributor
Posts: 110

Re: Multiple if then / else then statement in one argument

Sorry this is the correct output table (I have updated the table in the question as well):

sharesshroutshrout1shrout2WANT
262324095359850997509970000.000489428878
1062363010097.161407430.001052157076
12985791..676591600.00019193
3653140.21212212129480.00017222
5856168.41221.0.000142068
5856168....

 

@kiranv_ what do you think about the modified version of your code? If it works, maybe you can update your reply and I can accept it as an answer so that others can make use of it as well. 

proc sql;
	create table work.want as
	select shares, case when coalesce(shrout,shrout1) ^=. then
	shares/(coalesce(shrout,shrout1)*1000*1000)
	else shares/(shrout2*1000)
	end as share_rates from work.sample;
quit;

This produces the exact values. Could you please provide some details about what the "coalesce" statement does? Does it take into account for the missing value order? So, let's say if both shrout and shrou1 are not missing, then will the code use shrout instead of shrout1? In other words, what exactly does the "coalesce" statement do?

 

 

Super User
Posts: 19,815

Re: Multiple if then / else then statement in one argument


Yegen wrote:

In other words, what exactly does the "coalesce" statement do?

 


RTM:

 

COALESCE: Returns the first nonmissing value from a list of numeric arguments.

 

http://support.sas.com/documentation/cdl/en/lefunctionsref/69762/HTML/default/viewer.htm#p1vjttz6nua...

Super User
Posts: 11,343

Re: Multiple if then / else then statement in one argument

From the online documentation:

 

Coalesce function Returns the first nonmissing value from a list of numeric arguments. 

 

The order the variables appear in the list is import as it looks from left to right.

Frequent Contributor
Posts: 110

Re: Multiple if then / else then statement in one argument

Thanks for the clarification, @ballardw. Also, thank you for the helpful link, @Reeza.

☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 136 views
  • 3 likes
  • 4 in conversation