Dear all,
I hae to calculate counts and exposures so that the defaults are counted only once (when default=1) not twice. I wrote the code but apparently it does not take into account default column. Is there anyway to add an If statement to proc sql statement to my code?
TIME_ID | Instrument_ID | Exposure | BTM_Borrower_Rtg | INDUSTRY_CAT | INDUSTRY_SEG | lag_RG | lead_RG | default_flag | default |
200501 | Company A | 10 | 61 | . | 0 | ||||
200502 | Company A | 5 | 61 | . | 0 | ||||
200909 | Company A | 2 | 82 | . | 0 | ||||
200910 | Company A | 3 | 9 | 82 | 9 | 1 | 1 | ||
200911 | Company A | 1 | 9 | . | . | ||||
200501 | Company B | 2 | 61 | . | 0 | ||||
200502 | Company B | 2 | 61 | . | 0 | ||||
200909 | Company B | 2 | 82 | . | 0 | ||||
200910 | Company B | 2 | 9 | 82 | 9 | 1 | 1 | ||
200911 | Company B | 2 | 9 | . | . |
My code is:
proc sql;
create table total_exposure as
select time_id, count(*) as instr_ct, sum(exposure) as total_exposure
from data.sql_test
group by time_id
order by time_id;
quit;
But I want to get this table instead ( i do not want to count the defaults when default is .)
Time_ID | Count | Exposure |
200501 | 2 | 12 |
200502 | 2 | 7 |
200909 | 2 | 4 |
200910 | 2 | 5 |
200911 | 0 | 3 |
If logic in SQL is implemented via a CASE statement.
In this case you likely want a Where instead, after your from statement.
Where not missing(default)
Dear Reeza,
It works! Thank you so much! Awesome!
If I still want to display "0' for that year, do you know what should I do?
The code runs:
proc sql;
create table total_exposure as
select time_id, count(*) as instr_ct, sum(exposure) as total_exposure
from data.sql_test
where not missing (default)
group by time_id
order by time_id;
quit;
try this
proc sql;
create table total_exposure as
select time_id
, sum( ^missing (default) ) as instr_ct
, sum(exposure* (^missing (default))) as total_exposure
from sql_test
group by time_id
order by time_id;
quit;
count(*) counts observations, count(x) counts non-missing x values. Group by inplies order by. So
proc sql;
select
time_id,
count(default) as instr_ct,
sum(exposure) as total_exposure
from test
group by time_id;
quit;
@PGStats Yes, better!
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.
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.