BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
4 REPLIES 4
LinusH
Tourmaline | Level 20
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
Flip
Fluorite | Level 6
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
Super 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
deleted_user
Not applicable
Thank you all for the help!

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 4 replies
  • 911 views
  • 0 likes
  • 4 in conversation