Just to illustrate the usefulness of a long layout:
data table1;
infile datalines dlm='09'x;
input (x y z) ($) '2019-01'n '2019-02'n '2020-06'n '2021-01'n;
datalines;
a b c 1 2 1 10
a b d 0 3 1 11
b a d 2 1 3 8
a c b 1 4 5 2
;
data table2;
infile datalines dlm='09'x;
input (x y z) ($) '2019-01'n '2019-05'n '2020-07'n '2021-01'n '2021-02'n;
datalines;
a b c 0 0 1 8 11
a b d 1 4 3 2 20
b a d 0 1 0 0 2
a e e 0 0 0 2 1
;
proc transpose data=table1 out=t1;
by x y z notsorted;
var _numeric_;
run;
data t1_dt;
set t1;
date = input(cats(_name_,"-01"),yymmdd10.);
format date yymmd7.;
drop _name_;
rename col1=value;
run;
proc transpose data=table2 out=t2;
by x y z notsorted;
var _numeric_;
run;
data t2_dt;
set t2;
date = input(cats(_name_,"-01"),yymmdd10.);
format date yymmd7.;
drop _name_;
rename col1=value;
run;
data all;
set
t1_dt
t2_dt
;
run;
Once your data is in proper shape, it's simple:
proc summary data=all nway;
class x y z date;
var value;
output out=want (drop=_:) sum()=;
run;
And if you need a report out of the raw data, PROC REPORT does the sum on the fly:
proc report data=all;
column x y z value,date;
define x / group;
define y / group;
define z / group;
define value / "" sum;
define date / "" across;
run;
If you wrap that PROC REPORT into ODS EXCEL, you get an Excel file with the wide structure that even your boss can read. But as long as you need to do analysis, avoid the wide structure. See Maxim 19.
... View more