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 / (shrout*1000*1000) |
10623630 | 10097 | . | 16140743 | 0.001052157076 | shares / (shrout*1000*1000) |
12985791 | . | . | 67659160 | 0.00019193 | shares / (shrout2*1000) |
3653140 | . | 21212 | 21212948 | 0.00017222 | shares / (shrout1*1000*1000) |
5856168 | . | 41221 | . | 0.000142068 | shares / (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.
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;
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;
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;
@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 / (shrout*1000*1000) |
Shrout = . And Shrout1^=. | shares / (shrout1*1000*1000) |
Shrout = . And Shrout1=. | shares / (shrout2*1000) |
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?
@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.
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.