Help using Base SAS procedures

Proc SQL: Case Expression.

Reply
N/A
Posts: 0

Proc SQL: Case Expression.

I have a data set which includes a variable called "value_held". This variable is numeric and it contains both missing and non-missing values. For non-missing values, I'd like to replace them with an expression which is (price * shr_held), i.e. price * # shares held.

To my surprise, after running the SQL queries, all observations of value_held equal 1. How come? I've read the SAS 9.1 SQL Users Guide, but still have no clue. Everything is equal to 1 for all observations of value_held!!! Can you please help? Thanks a lot.

Here is my SQL program:

/* Merge MF holdings with stock prices */
proc sql feedback inobs=100000;
create table luong.mf_holdings as
select owner_id, fund_country_id, fund_country_name,
year, coalesce(f.rep_date, p.price_date) as rep_date format=ddmmyy10.,
coalesce(f.security_id, p.security_id) as security_id,
cusip, sedol, isin, ticker_sym,
firm_country_id, firm_country_name,
shr_held,
case value_held
when value_held = . then value_held = price * shr_held
else value_held = value_held
end as value_held,
price
from mf_holdings2 as f, price.price_hist_all as p
where f.security_id = p.security_id
and f.rep_date = p.price_date
order by owner_id, security_id, firm_country_id;
quit; Message was edited by: hoangluong
Super User
Posts: 5,437

Re: Proc SQL: Case Expression.

Posted in reply to deleted_user
For starters, your description doesn't match you program logic. My guess is that your program has the correct logic.
It would be easier to help if you could attach some sample data.
/Linus
Data never sleeps
Super Contributor
Posts: 359

Re: Proc SQL: Case Expression.

Posted in reply to deleted_user
You are mixing 2 forms of the CASE opperator.
use
case
when value_held = . then value_held = price * shr_held
else value_held
end as value_held,

OR

case value_held
when . then price * shr_held
else value_held
end as value_held,

Or just use COALESCE(value_held, price*shr_held);
Super User
Posts: 10,044

Re: Proc SQL: Case Expression.

Posted in reply to deleted_user
Hi. After Flip's suggestion.
your sql statement

" case value_held
when value_held = . then value_held = price * shr_held
else value_held = value_held
end as value_held,
price "

Should be

"price,case value_held
when value_held = . then value_held = price * shr_held
else value_held = value_held
end as value_held"


Because the calculated column should be behind the column used to calculate.

Ksharp
N/A
Posts: 0

Re: Proc SQL: Case Expression.

Posted in reply to deleted_user
Thank you all for the help!
Ask a Question
Discussion stats
  • 4 replies
  • 204 views
  • 0 likes
  • 4 in conversation