using Proc Sql to calculate multiple summary based on different conditions in one step

[fdb00004]

My intention here is to calculate two averages.

1. Before a certain dates and another

2. After certain dates.

Using proc SQL in SAS. I have done this seperately and then merge. How can i simplify the code with just one Proc Sql step.

Thank you.

proc SQL;

create table AvgWindPressure as 

select distinct MinPressure, avg(MaxwindMPH) as avgWind_before from work.Storm

where up_date < end_date

group by MinPressure, enddate;

quit;

proc SQL;

create table AvgWindpressure2 as 

select distinct MinPressure, avg(MaxwindMPH) as avg_after
from MaxwindMPH
where up_date > enddate

group by MinPressure, enddate;

quit;

[Kurt_Bremser]

Do you really have a column update and a column up_date in your dataset?

To get good help fast, it is imperative to present your source data in a usable way (data step with datalines).

[PaigeMiller]

Here's an example using data set SASHELP.CLASS

proc sql;
	create table want as select distinct sex,
		mean(case when age<14 then height else . end) as mean_before,
		mean(case when age>=14 then height else . end) as mean_after
		from sashelp.class
		group by sex;
quit;

It's also quite easy to do using PROC SUMMARY.

[fdb00004]

sorry the update column was a mistake only up_date column in dataset

[Kurt_Bremser · Accepted solution]

proc sql;
create table AvgWindPressure as
  select
    MinPressure,
    avg(case when up_date < end_date then MaxwindMPH else . end) as avgWind_before,
    avg(case when up_date >= end_date then MaxwindMPH else . end) as avgWind_after
  from work.Storm
  group by MinPressure
;
quit;

No DISTINCT clause is needed, GROUP BY will take care of that.