Proc SQL: Case Expression.

[deleted_user]

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

[LinusH]

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

[Flip]

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);

[Ksharp]

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

[deleted_user]

Thank you all for the help!