DATA Step, Macro, Functions and more

If statement in proc sql command

Reply
Contributor
Posts: 41

If statement in proc sql command

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

 

 

Super User
Posts: 17,824

Re: If statement in proc sql command

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)

Contributor
Posts: 41

Re: If statement in proc sql command

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;

PROC Star
Posts: 1,561

Re: If statement in proc sql command

[ Edited ]

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;
Respected Advisor
Posts: 4,646

Re: If statement in proc sql command

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
PROC Star
Posts: 1,561

Re: If statement in proc sql command

[ Edited ]
Ask a Question
Discussion stats
  • 5 replies
  • 492 views
  • 1 like
  • 4 in conversation