How to KEEP duplicates in Proc SQL, aggregating for quarter

[User12321]

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!

[Kurt_Bremser · Accepted solution]

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;