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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.