topic Re: If statement in proc sql command in SAS Programming

If statement in proc sql command

yelena — Tue, 14 Jun 2016 22:23:43 GMT

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

Re: If statement in proc sql command

Reeza — Tue, 14 Jun 2016 22:38:26 GMT

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)

Re: If statement in proc sql command

yelena — Tue, 14 Jun 2016 22:43:18 GMT

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;

Re: If statement in proc sql command

ChrisNZ — Wed, 15 Jun 2016 00:40:18 GMT

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;

Re: If statement in proc sql command

PGStats — Wed, 15 Jun 2016 02:06:55 GMT

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;

Re: If statement in proc sql command

ChrisNZ — Wed, 15 Jun 2016 06:06:45 GMT

@PGStats Yes, better!