topic Re: using Proc Sql to calculate multiple summary based on different conditions in one step in New SAS User

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

fdb00004 — Wed, 01 Jul 2020 11:28:57 GMT

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;

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

Kurt_Bremser — Wed, 01 Jul 2020 11:01:02 GMT

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).

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

PaigeMiller — Wed, 01 Jul 2020 11:06:33 GMT

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.

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

fdb00004 — Wed, 01 Jul 2020 11:11:44 GMT

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

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

Kurt_Bremser — Wed, 01 Jul 2020 11:21:12 GMT

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.