topic Re: Combine PROC SQL code blocks that have different where statements in SAS Programming

Combine PROC SQL code blocks that have different where statements

gzr2mz39 — Fri, 21 Aug 2020 23:13:49 GMT

Is there a way to combine these 3 PROC SQL code blocks into 1 PROC SQL code block? The only 2 parts of the SQL code that change are the name of the count(x) variable and the date range used in the where statement.

PROC SQL;
CREATE TABLE comp_date_range AS
SELECT company,
count(x) as ee_quarter20_count
from company_data
where doi between '01Aug2020'd and today()
group by company
;
run;quit;

PROC SQL;
CREATE TABLE comp_date_range1 AS
SELECT company,
count(x) as ee_quarter19_count
from company_data
where doi between '01Aug2019'd and intnx('year',today(),-1,'same')
group by company
;
run;quit;

PROC SQL;
CREATE TABLE comp_date_range2 AS
SELECT company,
count(x) as ee_ytd_count
from company_data
where doi between '01Jan2020'd and today()
group by company
;
run;quit;

Thank you.

Re: Combine PROC SQL code blocks that have different where statements

ChrisNZ — Sat, 22 Aug 2020 00:17:29 GMT

Like this?

select company
     , sum(doi between '01Aug2020'd and today()) as ee_quarter20_count
     , sum(doi between '01Aug2019'd and today()) as ee_quarter19_count
from company_data;
group by company;

Re: Combine PROC SQL code blocks that have different where statements

gzr2mz39 — Sat, 22 Aug 2020 01:19:44 GMT

Yes, thank you @ChrisNZ that's what I'm looking for.

But if I want to sum "x" then shouldn't it be like this?

PROC SQL;
CREATE TABLE contractors_date_range_a AS
SELECT company,
sum(case when doi between '01Aug2020'd and today() then x else 0 end) as ee_quarter_count
from company_data
group by company
;
run;quit;

Re: Combine PROC SQL code blocks that have different where statements

PGStats — Sat, 22 Aug 2020 03:17:59 GMT

You could define the summarizing periods in a separate dataset:

data periods;
startDate =  '01Aug2020'd; endDate = today(); period = "Qtr 20"; output;
startDate =  '01Aug2019'd; endDate = intnx('year',today(),-1,'same'); period = "Qtr 19"; output;
startDate =  '01Jan2020'd; endDate = today(); period = "Ytd"; output;
run;

proc sql;
create table comp_date as
select
	period,
	company,
	count(x) as count_x
from
	periods, company_data
where doi between startDate and endDate
group by period, company;
quit;

Re: Combine PROC SQL code blocks that have different where statements

Tom — Sat, 22 Aug 2020 17:50:00 GMT

@gzr2mz39 wrote:

Yes, thank you @ChrisNZ that's what I'm looking for.

But if I want to sum "x" then shouldn't it be like this?

PROC SQL;
CREATE TABLE contractors_date_range_a AS
SELECT company,
sum(case when doi between '01Aug2020'd and today() then x else 0 end) as ee_quarter_count
from company_data
group by company
;
run;quit;

Yes. If you wanted change the program to generate the sum of X instead of just counting the number of non-misisng values of X.

Note if you did still want the count then you might use:

count(case when (doi between '01Aug2020'd and today()) then x else null end)