@anweinbe:
At any rate, you'll need 2 passes through the data regardless of the method you choose. Below, the first pass is used to find the distinct names of the output summary variables for each [Company,Year]; then the second pass is used to aggregate directly into the output format you need.
data have ;
input Company :$16. Year Country:$16. Code Total ;
cards ;
Amazon 2004 USA 1 100
Amazon 2004 USA 1 142
Amazon 2003 USA 2 172
Amazon 2004 USA 2 178
Amazon 2006 USA 1 158
Amazon 2003 USA 3 177
Amazon 2005 Argentina 4 146
Amazon 2004 Cuba 3 182
Amazon 2005 England 2 188
Amazon 2006 France 1 110
Amazon 2005 Canada 4 176
Amazon 2005 Mexico 3 113
Sony 2003 USA 2 184
Sony 2003 USA 1 148
Sony 2003 USA 3 177
Sony 2005 USA 2 178
Sony 2006 USA 1 184
Sony 2005 Argentina 2 122
Sony 2006 Cuba 4 129
Sony 2006 England 2 109
Sony 2003 France 3 103
Sony 2004 Canada 3 158
Sony 2005 Mexico 1 187
;
run ;
%let dfc_expr = catx ("_", ifc (country="USA", "Domestic", "Foreign"), cats ("Code", code)) ;
proc sql noprint ;
select distinct &dfc_expr into :dfc separated by " " from have ;
quit ;
data _null_ ;
if _n_ = 1 then do ;
array ss &dfc ;
dcl hash h (ordered:"a") ;
h.definekey ("company", "year") ;
h.definedata ("company", "year") ;
do over ss ;
h.definedata (vname (ss)) ;
end ;
h.definedone() ;
end ;
set have end = end ;
if h.find() ne 0 then call missing (of ss[*]) ;
do over ss ;
if vname (ss) ne &dfc_expr then continue ;
ss ++ total ;
leave ;
end ;
h.replace() ;
if end then h.output (dataset:"want") ;
run ;
Note that the names of the summary variables are created in the pattern of Domestic_Code1, Foreign_Code2, and so forth. You can change it to your liking by changing the expression assigned to the macro variable DFC_EXPR. Also note that if some combination of Company, Year, and DFC is not in the input data, you'll get a missing value in that cell. If you want it to be a zero, change the code line with CALL MISSING to:
if h.find() ne 0 then do over ss ;
ss = 0 ;
end ;
Kind regards
Paul D.
