BookmarkSubscribeRSS Feed
yelena
Fluorite | Level 6

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_IDInstrument_IDExposureBTM_Borrower_RtgINDUSTRY_CATINDUSTRY_SEGlag_RGlead_RGdefault_flagdefault
200501Company A1061    .0
200502Company A561    .0
200909Company A282    .0
200910Company A39  82911
200911Company A19    ..
200501Company B261    .0
200502Company B261    .0
200909Company B282    .0
200910Company B29  82911
200911Company B29    ..

 

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_IDCountExposure
200501212
20050227
20090924
20091025
20091103

 

 

5 REPLIES 5
Reeza
Super User

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)

yelena
Fluorite | Level 6

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;

ChrisNZ
Tourmaline | Level 20

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;
PGStats
Opal | Level 21

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

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!

How to Concatenate Values

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.

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
  • 5 replies
  • 2216 views
  • 1 like
  • 4 in conversation