How to KEEP duplicates in Proc SQL, aggregating for quarter

User12321 — Tue, 14 Jul 2020 06:01:10 GMT

I am trying to aggregate data for the quarter from months.

Data is quite simple has Type, Code and then values (revenue, quantity, label etc.).

Occasionally some lines in month 1 match exactly lines in month 2 or month 3 of the quarter. I just discovered that in that case data from one of the month is dropped ( i.e. duplicates are eliminated). I need to keep all data! Can this be done with proc sql?

Current program in the nutshell:

proc sql;

create table Q as

select type, code, sum (revenue) as Revenue, sum(quantity) as Quantity, label

from

(select * from month1 union

select * from month2 union

select * from month3)

where code is not null

group by type, code, label,

order by type , code;

quit;

Thanks!

Re: How to KEEP duplicates in Proc SQL, aggregating for quarter

Kurt_Bremser — Tue, 14 Jul 2020 07:07:11 GMT

You have to use UNION ALL to keep duplicates; unless you want to explicitly remove duplicates, it is always recommended to use UNION ALL, because it needs one less sort.

Your issue can also be solved with these two steps:

data temp / view=temp;
set
  month1
  month2
  month3
;
keep type code revenue quantity label;
where code ne " "; /* use . if code is numeric */
run;

proc summary data=temp nway;
class type code label;
var revenue quantity;
output
  out=want (drop=_type_ _freq_)
  sum()=
;
run;

topic How to KEEP duplicates in Proc SQL, aggregating for quarter in New SAS User

How to KEEP duplicates in Proc SQL, aggregating for quarter

Re: How to KEEP duplicates in Proc SQL, aggregating for quarter