BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Yegen
Pyrite | Level 9

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
kiranv_
Rhodochrosite | Level 12

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

7 REPLIES 7
Yegen
Pyrite | Level 9

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;
kiranv_
Rhodochrosite | Level 12

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;

 

 

Yegen
Pyrite | Level 9

@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)
Yegen
Pyrite | Level 9

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?

 

 

Reeza
Super User

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

ballardw
Super User

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.

Yegen
Pyrite | Level 9

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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