topic Re: Multiple if then / else then statement in one argument in SAS Programming

Multiple if then / else then statement in one argument

Yegen — Thu, 01 Jun 2017 22:08:44 GMT

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:

shares	shrout	shrout1	shrout2	WANT	METHOD USED
26232409	53598	50997	50997000	0.000489428878	shares / (shrout10001000)
10623630	10097	.	16140743	0.001052157076	shares / (shrout10001000)
12985791	.	.	67659160	0.00019193	shares / (shrout2*1000)
3653140	.	21212	21212948	0.00017222	shares / (shrout110001000)
5856168	.	41221	.	0.000142068	shares / (shrout110001000)
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.

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

Yegen — Thu, 01 Jun 2017 21:33:54 GMT

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;

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

kiranv_ — Thu, 01 Jun 2017 22:20:06 GMT

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;

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

Yegen — Thu, 01 Jun 2017 21:57:55 GMT

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

If	Then
Shrout ^= .	shares / (shrout10001000)
Shrout = . And Shrout1^=.	shares / (shrout110001000)
Shrout = . And Shrout1=.	shares / (shrout2*1000)

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

Yegen — Thu, 01 Jun 2017 22:05:43 GMT

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

shares	shrout	shrout1	shrout2	WANT
26232409	53598	50997	50997000	0.000489428878
10623630	10097	.	16140743	0.001052157076
12985791	.	.	67659160	0.00019193
3653140	.	21212	21212948	0.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?

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

Reeza — Thu, 01 Jun 2017 22:11:04 GMT

@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#p1vjttz6nuankzn1gh4z3wgcu0bf.htm

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

ballardw — Thu, 01 Jun 2017 22:12:59 GMT

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.

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

Yegen — Thu, 01 Jun 2017 23:04:49 GMT

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