topic Re: The SAS variant to SQL for each X do Y. in SAS Programming

The SAS variant to SQL for each X do Y.

Yves_Boonen — Mon, 18 Apr 2016 13:41:02 GMT

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?

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

FreelanceReinh — Mon, 18 Apr 2016 13:46:04 GMT

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;

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

RW9 — Mon, 18 Apr 2016 13:49:01 GMT

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;

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

Astounding — Mon, 18 Apr 2016 13:49:04 GMT

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;

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

Yves_Boonen — Mon, 18 Apr 2016 13:58:08 GMT

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.

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

Reeza — Mon, 18 Apr 2016 14:16:22 GMT

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.