Solved
Contributor
Posts: 62

# The SAS variant to SQL for each X do Y.

[ Edited ]

I have been wondering about this for a while, and I haven't been able to find any answers online. The following data is fictional, but let's say you have this table:

 zone reports oldest_date1 accidents oldest_date2 ZONE1 5 31/01/2016 2 31/01/2016 ZONE1 2 29/01/2016 1 29/01/2016 ZONE1 7 27/01/2016 0 27/01/2016 ZONE1 3 3/02/2016 1 3/02/2016 ZONE2 2 31/01/2016 3 31/01/2016 ZONE2 9 29/01/2016 4 29/01/2016 ZONE2 8 27/01/2016 5 27/01/2016 ZONE2 4 3/02/2016 0 3/02/2016 ZONE3 2 31/01/2016 8 31/01/2016 ZONE3 1 29/01/2016 1 29/01/2016 ZONE3 3 27/01/2016 0 27/01/2016 ZONE3 3 3/02/2016 0 3/02/2016

Let's say you wanted to create a query that counts all the reports and accidents for each zone and remembers the lowest date for each report and accident for each zone. The result table should look like this:

 zone reports oldest_date1 accidents oldest_date2 ZONE1 17 27/01/2016 4 27/01/2016 ZONE2 23 27/01/2016 12 27/01/2016 ZONE3 9 27/01/2016 9 27/01/2016

In SQL you could easily approach this with something like this:

for each zone (sum(reports), min(oldest_date1), sum(accidents), min(oldest_date2))

And there you have it, you will receive a table like the one above. SAS seems devious when you want to do something like that and (for as far as I know) requires you to write a complex datastep. Is this true? Or could someone enlighten me?

Accepted Solutions
Solution
‎04-18-2016 09:57 AM
Posts: 1,256

## Re: The SAS variant to SQL for each X do Y.

Hi Yves,

You need the GROUP BY clause:

``````proc sql;
create table want as
select zone, sum(reports) as reports, min(oldest_date1) as oldest_date1 format=ddmmyy10.,
sum(accidents) as accidents, min(oldest_date2) as oldest_date2 format=ddmmyy10.
from have
group by zone;
quit;``````

All Replies
Solution
‎04-18-2016 09:57 AM
Posts: 1,256

## Re: The SAS variant to SQL for each X do Y.

Hi Yves,

You need the GROUP BY clause:

``````proc sql;
create table want as
select zone, sum(reports) as reports, min(oldest_date1) as oldest_date1 format=ddmmyy10.,
sum(accidents) as accidents, min(oldest_date2) as oldest_date2 format=ddmmyy10.
from have
group by zone;
quit;``````
Super User
Posts: 9,599

## Re: The SAS variant to SQL for each X do Y.

Not sure that the SQL variant is much shorted code wise than a datastep:

```data want;  /* Drop or rename as you need */
set have;
by zone;
retain sum_reports min_date1 sum_accidents min_date2;
if first.zone then do;
sum_reports=reports;
min_date1=oldest_date1;
sum_accidents=accidents;
min_date2=oldest_date2;
end;
else;
sum_reports=sum(sum_reports,reports);
min_date1=ifn(oldest_date1 < min_date1,oldest_date1,min_date1);
sum_accidents=sum(sum_accidents,accidents);
min_date2=ifn(oldest_date2 < min_date2,oldest_date2,min_date2);
end;
if last.zone then output;
run;```
Super User
Posts: 6,785

## Re: The SAS variant to SQL for each X do Y.

A complex DATA step is not needed.  PROC SUMMARY can handle this.  Since it appears that your data set is sorted by ZONE, I will use that in the solution.  Workarounds are possible if that's not the case.

proc summary data=have;

by zone;

var reports oldest_date1 accidents oldest_date2;

output out=want (drop=_type_ _freq_) sum(reports)=reports sum(accidents)=accidents min(oldest_date1)=oldest_date1

min(oldest_date2)=oldest_date2;

run;

Contributor
Posts: 62

## Re: The SAS variant to SQL for each X do Y.

How could I forget about "group by". That basically means for each, but I find it very unnatural that you do that in one of your last steps.

Super User
Posts: 23,771

## Re: The SAS variant to SQL for each X do Y.

Proc summary/means is the equivalent.

Think of the case where you want multiple levels, ie group by and total, or subtotals, proc means can incorporate that. Proc SQL cannot, without multiple queries.

🔒 This topic is solved and locked.