@eric23263 wrote:
It's not the same question, it's with 2 tables
I can't do that
proc summary data=TDB_bul LEFT JOIN pour_TDB.Quater_MF ON TDB_bul.AnSuiv = Quater_MF.Annee_suivie AND TDB_bul.Code_diplome = Quater_MF.Code_diplome ;
class Code_diplome AnSuiv ;/*2 columns from TDB_bul*/
var eff_app_total17 Masc_2015 ; /*67 columns from TDB_bul and Quater_MF*/
output out=pour_TDB.TDB25 sum= ;
run;
No you can't proc summary works on one data set.
Without example data it is very hard to make suggestions. However for summing things creating the summaries from each of the input data sets separately would reduce the number of records being joined. Also Proc summary (or means ) allows use of variable lists so the code can be much shorter if you don't have to list 300 variables by name with the sum(var) as var structure required by SQL.
Since your sql code did not include sum(table.variable) I had to assume that each of the variables being summed only comes from one data set. So that set should be quite amenable to Proc Summary. If the variables are mixed between the two sets the summarize each. Something like
proc summary data=settosum nway;
class Code_diplome AnSuiv;
var eff: ;
output out=summary(drop=_type_ _freq_) sum= ;
run;
proc summary data=settosum2 nway;
class Code_diplome AnSuiv;
var eff: ;
output out=summary2(drop=_type_ _freq_) sum= ;
run;
proc sql;
create table want as
select <stuff>
from summary as a
left join
summary2 as b
on <criteria>
;
quit;
The error messages from your Proc Summary should tell that you were not using the tool (proc summary) correctly.
I realize that many people have a background in SQL and try to do everything there because of familiarity but the SAS environment as a LOT of tools and some of them are much more efficient to execute, or simpler to code (the proc summary example here). These other tools have there own rules and it helps to learn them.
I do not try to force data steps to do a Cartesian join on multiple variables because SQL is almost always the best tool for that. Similarly summarizing data with another procedure before merging is a common approach.
Also note that with a very simple change to the code from proc summary I can the sums of those variables for 1)all the records, 2) all the values of Code_diplome, 3) all the values of AnSuiv as well as 4) only the combinations of Code_diplome and AnSuiv.
How simple: remove the NWAY option (though with that you want to keep the _type_ variable.
Add std= mean= max= min= /autoname after the sum= in the output statement of proc summary and I now have those statistics and the variables will be named with the original variable name with _sum _std _mean _max and _min appended. Without having to type out 65 (or 300 or what have you) std(var) as var_std, mean(var) as var_mean etc statements from SQL.
Even if Proc summary ran a little slower the time I saved by using variable lists and autoname might make the solution faster than SQL.
