Summary Statistics for Different Variables for different Groups using SQL in SAS

[taygehring]

This is the code I'm using. I keep getting a lot of errors. 

proc sql;

select 

min(logat) as 'MinLogofTotalAssets', 

min(roa) as 'MinReturnofAssets', 

min(bus_seg) as 'MinBusinessSegment', 

min(geo_seg) as 'MinGeographicSegment', 

min(ln_nas) as 'MinLNofNAS',

min(city_ind_fees) as 'MinCityIndependentFees', 

min(big4) as 'MinBigFour',

min(audit_tenure) as 'MinAuditTenure',

max(logat) as 'Max Log of Total Assets', 

max(roa) as 'Max of Return of Assets', 

max(bus_seg) as 'Max of Business Segment', 

max(geo_seg) as 'Max Geographic Segment', 

max(ln_nas) as 'Max LN of NAS',

max(city_ind_fees) as 'Max City Independent Fees', 

max(big4) as 'Max Big Four',

max(audit_tenure) as 'Max Audit Tenure',

mean(logat) as 'Mean Log of Total Assets', 

mean(roa) as 'Mean Return of Assets', 

mean(bus_seg) as 'Mean Business Segment', 

mean(geo_seg) as 'Mean Geographic Segment', 

mean(ln_nas) as 'Mean of LN NAS',

mean(city_ind_fees) as 'Mean of City Independent Fees', 

mean(big4) as 'Mean of Big Four',

mean(audit_tenure) as 'Mean of Audit Tenure',

median(logat) as 'Median Log of Total Assets', 

median(roa) as 'MedianReturnofAssets', 

median(bus_seg) as 'Median Business Segment', 

median(geo_seg) as 'Median Geographic Segment', 

median(ln_nas) as 'Median of LN NAS',

median(city_ind_fees) as 'Median of City Independent Fees', 

median(big4) as 'Median of Big Four',

median(audit_tenure) as 'Median of Audit Tenure',

std(logat) as 'STD Log of Total Assets', 

std(roa) as 'STD Return of Assets', 

std(bus_seg) as 'STD Business Segment', 

std(geo_seg) as 'STD Geographic Segment', 

std(ln_nas) as 'STD of LN NAS',

std(city_ind_fees) as 'STD of City Independent Fees', 

std(big4) as 'STD of Big Four',

std(audit_tenure) as 'STD of Audit Tenure',

var(logat) as 'Var Log of Total Assets', 

var(roa) as 'Var Return of Assets', 

var(bus_seg) as 'Var Business Segment', 

var(geo_seg) as 'Var Geographic Segment', 

var(ln_nas) as 'Var of LN NAS',

var(city_ind_fees) as 'Var of City Independent Fees', 

var(big4) as 'Var of Big Four',

var(audit_tenure) as 'Var of Audit Tenure'

from audit

group by 'Group 1';

quit;

Thanks for you help in advance!

[VDD]

are these  'Group 1' valid variable names? 

[RW9]

Sorry, that code made me chuckle.  We were just discussing how SQL seems to be taking over from Base SAS programming, and that illustrates it only too well.  You have effectively hand coded a proc means, and possibly a proc transpose in one long statement.  And to cap it all are using named literals for no apparent reason, or with reference to the syntax. 

So to answer why you code produces errors, named literals have to be quoted, and finish with an n:

min(logat) as 'MinLogofTotalAssets'n,

No to fix using named literals at all, as its not needed and messy:

min(logat) as MinLogofTotalAssets,

However, simplifying to:

proc means data=audit;
  class group1;
  var logat roa bus_seg...;
  output out=want min= max= mean= median= stddev= / autoname;
run;

(note not sure what the var() equivalent is, perhaps one of the statos can advise), would do the same, and probably by in the high percentage times faster than your code.

[ballardw]

---

@RW9 wrote:

Sorry, that code made me chuckle.  We were just discussing how SQL seems to be taking over from Base SAS programming, and that illustrates it only too well.  You have effectively hand coded a proc means, and possibly a proc transpose in one long statement.  And to cap it all are using named literals for no apparent reason, or with reference to the syntax. 

However, simplifying to:

proc means data=audit;
  class group1;
  var logat roa bus_seg...;
  output out=want min= max= mean= median= stddev= / autoname;
run;

(note not sure what the var() equivalent is, perhaps one of the statos can advise), would do the same, and probably by in the high percentage times faster than your code.

---

VAR, variance, would be VAR in proc means.

And I had the same reaction that Proc Means or Summary may be more appropriate and certainly shorter to code.

@taygehring may also be interested to know that the statistics when using proc means/ summary will inherit labels as well.

Using: var _numeric_ ; will do the summary of ALL of the numeric variables in the data set.

Also other variable lists can be used on Class or Var that Proc SQL will not.

[Astounding]

If you want to refer to a variable name in SAS,  you don't need quotes.  Correct:

min(logat) as MinLogofTotalAssets, 

If you want to refer to a variable name that includes special characters, such as blanks as part of the name, you need to add the letter "n":

var(audit_tenure) as 'Var of Audit Tenure'n

Using special characters as part of variable names gives you the headache later of referring to those variable names with the quotes and with the "n" at the end.

[VDD]

thanks @Astounding for the reminder about using the n.

I don't do myself very often, since I came from the old world of only using only 8 charters.