topic Re: proc sql & sum in SAS Procedures

proc sql & sum

ywon111 — Wed, 08 Sep 2021 02:18:41 GMT

I think this is a simple one.

This is what I have

date	Type	Count
Jan-21	A	100
Jan-21	B	200
Feb-21	A	300
Feb-21	B	400

This is what I want

date	Type	Count
Jan-21	A	100
Jan-21	B	200
Jan-21	C	300
Feb-21	A	300
Feb-21	B	400
Feb-21	C	700

Thanks

Re: proc sql & sum

japelin — Wed, 08 Sep 2021 03:21:18 GMT

How about this?

There is no format to display it as Jan-21, though, so it will be JAN21.

data have;
  input date:monyy6. Type $ Count;
  format date monyy6.;
cards;
Jan-21	A	100
Jan-21	B	200
Feb-21	A	300
Feb-21	B	400
;
run;

proc sort data=have;
  by date;
run;

data want;
  set have;
  by date;
  retain sum;
  if first.date then sum=0;
  sum=sum+count;
  output;
  if last.date then do;
    Type='C';
    Count=sum;
    output;
  end;
  drop sum;
run;

Re: proc sql & sum

mkeintz — Wed, 08 Sep 2021 03:24:00 GMT

If every date has exactly two observations as in your example, and if the data are sorted by date, then:

data want;
  set have;
  by date ;
  output;
  type='C';
  count+lag(count);
  if last.date then output;
run;

The "by date" statement not only tells SAS to expect the data to be sorted, but also generates two dummy variables: first.date and last.date, indicating whether the observation in hand is the first or last one for a given date.

The first output statement just outputs the current record, followed by modifying count to add the previous obs. The second output statement only outputs that modified count variable for the last obs of each date.

Re: proc sql & sum

Kurt_Bremser — Wed, 08 Sep 2021 08:10:39 GMT

A slightly different take, reading in a DO loop:

data want;
do until (last.date);
  set have;
  by date;
  output;
  _sum = sum(_sum,count);
end;
type = "C";
count = _sum;
output;
drop _sum;
run;

Note that SQL is very poor at processing sequences, it is aimed more at groups. So the tool of choice for your task is the data step (Maxim 14).

Re: proc sql & sum

Ksharp — Wed, 08 Sep 2021 12:26:27 GMT

data have;
  input date:monyy6. Type $ Count;
  format date monyy6.;
cards;
Jan-21	A	100
Jan-21	B	200
Feb-21	A	300
Feb-21	B	400
;

proc sql;
create table want as
select * from have
union
select date,'C',sum(count) from have group by date;
quit;