topic Re: Proc SQL: Case Expression. in SAS Procedures

Proc SQL: Case Expression.

deleted_user — Fri, 11 Jun 2010 06:27:51 GMT

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

Re: Proc SQL: Case Expression.

LinusH — Fri, 11 Jun 2010 08:07:00 GMT

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

Re: Proc SQL: Case Expression.

Flip — Fri, 11 Jun 2010 11:49:37 GMT

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

Re: Proc SQL: Case Expression.

Ksharp — Sat, 12 Jun 2010 07:14:35 GMT

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

Re: Proc SQL: Case Expression.

deleted_user — Thu, 17 Jun 2010 04:59:17 GMT

Thank you all for the help!