topic Re: Summary stats in SAS Programming

Summary stats

sivaji — Thu, 24 May 2012 19:18:01 GMT

I have 100 Variable in my dataset. I need to find n nmiss min max mean median for each variable..

When I tried writing proc means/summary with output statement it is giving only one record in out dataset(don't know which variable' summary stats is for). Is it not possible to get summary stats in to a dataset for all variables with one proc statement without using macros to pass each variable and appending it to the base table.

please help me on the same.

Sivaji.

Re: Summary stats

PGStats — Thu, 24 May 2012 19:27:11 GMT

Use autoname :

Proc means data=myData;

var _numeric_;

outpur out=myStats n= nmiss= min= max= mean= median= / autoname;

run;

Re: Summary stats

Linlin — Thu, 24 May 2012 20:00:53 GMT

should be:

output out=myStats n= nmiss= min= max= mean= median= / autoname;

PG, have you made changes to your Excel macro? Thanks - Linin

Re: Summary stats

PGStats — Thu, 24 May 2012 20:11:56 GMT

Should be: Linlin !

Yes, I solved a few problems with the macro. Last update was on 2012-05-20. Please let me know, if you try it again. Thanks.

Regards

Re: Summary stats

Linlin — Thu, 24 May 2012 22:59:39 GMT

Hi PG,

I downloaded your Macro a few minutes ago, the sas code has a modified date 05/17/2012. are you sure you put your updated version on line?

I have sas 9.3, Windons 7 and office 2010. Below is the log file.

31 %macro

31 ! ExcelLoad(ExcelPath=help,dsn=%scan(&SYSDSN,2),sheet=&dsn,lib=%scan(&SYSDSN,1))

31 ! ;

32 %let _dsn=&dsn;

33 %let _lib=&lib;

34 %let _sheet=&sheet;

35 %let _ExcelPath=&ExcelPath;

37 %if %quote(&_ExcelPath)=help %then %do;

38 %put NOTE: Use this macro to copy a SAS dataset with more than 255

38 ! variables to an Excel workbook.;

39 %put NOTE: Usage : ExcelLoad(ExcelPath=, lib=, dsn=, sheet=);

40 %put NOTE: ExcelPath : File path to Excel workbook;

41 %put NOTE: lib : SAS library (default: library of most recently created

41 ! dataset);

42 %put NOTE: dsn : SAS dataset name (default: most recently created dataset)

42 ! ;

43 %put NOTE: sheet : excel workbook sheet name to create (default: same as

43 ! dataset name);

44 %put NOTE: You cannot overwrite an existing sheet with ExcelLoad.;

45 %end;

46 %else %do;

48 %let colBlock=200;

49 %let _SYSDSN=&SYSDSN;

51 libname _xl Excel "&_ExcelPath";

53 proc sql noprint;

54 select count(*) into :sheetExists from

55 dictionary.tables where libname="_XL" and

56 (upcase(memname)=upcase("&_sheet.") or

56 ! upcase(memname)=upcase("&_sheet.$"));

57 quit;

59 %if &sheetExists %then %do;

60 %put ERROR: Sheet &_sheet already exists in workbook &_ExcelPath.;

61 %put ERROR: You must delete sheet &_sheet in Excel or choose another sheet

61 ! name.;

62 %end;

63 %else %do;

64 option nonotes;

65 proc fcmp outlib=sasuser.fcmp.excel;

66 function _ExcelCol(colNo) $4;

67 n = max(int(colNo),1);

68 length colStr $4;

69 colStr = "";

70 do while(n>0);

71 m = mod(n-1,26);

72 colStr = cat(byte(65+m),colStr);

73 n = int((n-m-1)/26);

74 end;

75 return (trim(colStr));

76 endsub;

77 run;

78 data _null_; call symput("restoreCMPLIB",getoption("CMPLIB","keyword"));

78 ! run;

79 options append=(cmplib=sasuser.fcmp);

81 data _null_;

82 call symput("nbVars", put(attrn(open("&_lib..&_dsn."),"NVARS"),6.0));

83 call symput("nbLines",

83 ! put(1+attrn(open("&_lib..&_dsn."),"NLOBS"),8.0));

84 run;

86 %let vBeg=1;

87 %do %while(&vBeg<=&nbVars);

88 %if &vBeg+&colBlock-1>&nbVars

89 %then %let vEnd=&nbVars;

90 %else %let vEnd=%eval(&vBeg+&colBlock-1);

92 proc sql noprint;

93 select name into :vars separated by ","

94 from dictionary.columns

95 where libname=upcase("&_lib") and upcase(memname)=upcase("&_dsn")

96 and varnum between &vBeg and &vEnd ;

97 quit;

98 %if &vBeg=1

99 %then %let tName=&_sheet;

100 %else %let

100! tName=&_sheet.$%sysfunc(compress(%sysfunc(_ExcelCol(&vBeg))1:%sysfunc(_ExcelCo

100! l(&vEnd))&nbLines));

101 proc sql;

102 create table _xl."&tName."n as select &vars. from &_lib..&_dsn.;

103 quit;

104 %let vBeg=%eval(&vBeg+&colBlock);

105 %end;

106 proc fcmp outlib=sasuser.fcmp.Excel;

107 deletefunc Excel._ExcelCol;

108 run;

109 options &restoreCMPLIB.;

110 option notes;

111 %put NOTE: Dataset &_lib..&_dsn was copied to sheet &_sheet in workbook

111! &_ExcelPath..;

112 %end;

113 libname _xl clear;

114 %let SYSDSN=&_SYSDSN;

115 %end;

116 %mend ExcelLoad;

117 data test;

118 array _v(*) var1-var300;

119 do i=1 to 300;

120 _v(i)=i;

121 end;

122 run;

NOTE: The data set WORK.TEST has 1 observations and 301 variables.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

123 %ExcelLoad(ExcelPath=c:\temp\forum\myFile.xlsx, lib=work, dsn=test,

123! sheet=mySheet);

NOTE: Libref _XL was successfully assigned as follows:

Engine: EXCEL

Physical Name: c:\temp\forum\myFile.xlsx

NOTE: PROCEDURE SQL used (Total process time):

real time 0.16 seconds

cpu time 0.03 seconds

ERROR: Error attempting to CREATE a DBMS table. ERROR: Execute: This table

contains cells that are outside the range of cells defined in this

spreadsheet..

WARNING: File deletion failed for _XL.'mySheet$GS1:KO2'n.DATA.

NOTE: Dataset work.test was copied to sheet mySheet in workbook

c:\temp\forum\myFile.xlsx.

NOTE: Libref _XL has been deassigned.

Re: Summary stats

PGStats — Thu, 24 May 2012 23:34:21 GMT

That's the old version. Did you get it from

https://communities.sas.com/docs/DOC-1260 ?

When I see it, it says modified on 2012-05-20 and is open to everyone. I just deleted the two archived versions. So, it should now be impossible to get an older version now. Thank you again Linlin.

Re: Summary stats

Linlin — Fri, 25 May 2012 00:51:29 GMT

Hi PG,

I tried but no luck. Maybe I am doing something wrong. I will email you the log file

https://communities.sas.com/docs/DOC-1260

Thanks - Linlin

Re: Summary stats

sivaji — Fri, 25 May 2012 09:20:31 GMT

Thanks for the help and it is giving it all columns info in a single row.. I need it by different rows for each variable.

Re: Summary stats

PGStats — Fri, 25 May 2012 15:54:25 GMT

If it's a table you want, then use proc tabulate :

proc tabulate data =myData format=best7.;

var _numeric_;

table (_numeric_),(n nmiss mean min max median);

run;

Re: Summary stats

data_null__ — Fri, 25 May 2012 16:51:45 GMT

Have you looked at the OUT= data from this TABULATE or even the ODS OUTPUT <don't know the name>=stats;

Re: Summary stats

PGStats — Fri, 25 May 2012 17:34:48 GMT

No I hadn't. But now that I do, I don't get the results that I expected.

The OUT= dataset contains only 1 obs with a variable for each combination of variable and statistic (i.e. one variable per cell in the table)
The ODS OUTPUT Table=myTable statement genetrates the WARNING "Data set definition for 'Table' has been overridden." and no dataset.

PROC TABULATE seems better at producing report tables than datasets.

Re: Summary stats

data_null__ — Sun, 27 May 2012 14:32:24 GMT

If you remove the OUT= then ODS OUTPUT will work. However they are both the same, mostly.

Re: Summary stats

Ksharp — Mon, 28 May 2012 04:28:47 GMT

It seems that proc tabulate is not so smart. Use SASHELP.CLASS as an example.

%macro mean(tname,vname);
Proc means data=&tname noprint;
var &vname ;
output out=temp(drop=_:) n=n nmiss=nmiss min=min max=max mean=mean median=median ;
run;
data temp;length vname $ 40;set temp;vname="&vname";run;
proc append base=want data=temp force;run;
%mend mean;
data _null_;
 set sashelp.vcolumn(where=(libname='SASHELP' and memname='CLASS' and type='num'))  ;
 call execute(cats('%mean(',libname,'.',memname,',',name,')'));
run;

Ksharp

Re: Summary stats

data_null__ — Thu, 31 May 2012 17:59:58 GMT

Looping over variables in a data set as you suggest is generally a bad idea. When the number of variables and obs is relatively small performance is not too bad but as either grow performance can suffer significantly.

In general it is better to let SAS summarize many variables at once. It is very good at that. However there is the problem of the output not in the format that is most pleasing. Of course that can be remedied more efficiently after the data are summarized as the number of obs to be process will be reduced.

proc summary data=sashelp.heart nway;

class sex;

var _numeric_;

output out=Stats0 n= nmiss= min= max= mean= median= / autoname;

run;

proc transpose data=stats0 out=stats1;

by sex _type_ _freq_;

run;

data stats1;

set stats1;

call scan(_name_,-1,p,l,'_');

length Variable $32 Statistic $8;

Variable = substr(_name_,1,p-2);

Statistic = substr(_name_,p);

run;

proc sort data=stats1;

by Sex Variable;

run;

proc transpose data=stats1 out=stats2(drop=_name_);

by Sex Variable;

var col1;

id Statistic;

idlabel Statistic;

run;

Proc print;

run;

Re: Summary stats

Ksharp — Fri, 01 Jun 2012 05:37:10 GMT

NULL,

No, I don't think your code is good one. Since you mentioned lots of variables in a table.

In your code, every variable will generate four new variables, when a table has lots of variables (i.e. twenty thousand ), you think proc means; _numeric_ ; can hold so many variables? , and more ,you are using proc transpose which is undisputed way to slow down . Therefore, I don't think your code is better or faster than mine.

Ksharp

Re: Summary stats

Howles — Fri, 01 Jun 2012 13:37:37 GMT

See http://www.sascommunity.org/wiki/PROC_MEANS_-_Improve_on_the_default

Re: Summary stats

data_null__ — Fri, 01 Jun 2012 13:59:21 GMT

Did you ever fix the problem with 2-sided confidence intervals.

Re: Summary stats

data_null__ — Sat, 02 Jun 2012 02:21:47 GMT

Ksharp wrote:

NULL,

No, I don't think your code is good one. Since you mentioned lots of variables in a table.

Ksharp

I see you don't believe me as you seem to have preconceived notions of what kinds of SAS programs perform better than others.

I tested my program and found that it will summarize 20,000 variables although I think that is very close to the limit of my laptop computer's capacity. The program was able to summarize all variables and reformat the output into the “stats in columns variables in rows” format. It took about 34 seconds which seems pretty good. Using the loopy method took a little over 1 minute for 1000 variables I did not try 20,000 variables but I suppose that would be a least 20 minutes.

898 run;

899 options fullstimer=1;

900

901 data manyVars;

902 array v[20000];

903 do _n_ = 1 to 200;

904 do i = 1 to dim(v);

905 v=ranuni(12345);

906 end;

907 output;

908 end;

909 drop i;

910 run;

NOTE: The data set WORK.MANYVARS has 200 observations and 20000 variables.

NOTE: DATA statement used (Total process time):

real time 0.54 seconds

user cpu time 0.39 seconds

system cpu time 0.03 seconds

Memory 9322k

OS Memory 18732k

Timestamp 6/1/2012 8:42:01 PM

911

912

913 %let start = %sysfunc(datetime());

914 proc summary data=manyVars nway;

915 var _numeric_;

916 output out=Stats0 n= nmiss= min= max= mean= median= / autoname;

917 run;

NOTE: There were 200 observations read from the data set WORK.MANYVARS.

NOTE: The data set WORK.STATS0 has 1 observations and 120002 variables.

NOTE: PROCEDURE SUMMARY used (Total process time):

real time 32.24 seconds

user cpu time 9.45 seconds

system cpu time 10.84 seconds

Memory 324587k

OS Memory 340148k

Timestamp 6/1/2012 8:42:33 PM

918 proc transpose data=stats0 out=stats1;

919 by _type_ _freq_;

920 run;

NOTE: There were 1 observations read from the data set WORK.STATS0.

NOTE: The data set WORK.STATS1 has 120000 observations and 4 variables.

NOTE: PROCEDURE TRANSPOSE used (Total process time):

real time 0.35 seconds

user cpu time 0.18 seconds

system cpu time 0.09 seconds

Memory 42809k

OS Memory 53092k

Timestamp 6/1/2012 8:42:34 PM

921 data stats1;

922 set stats1;

923 call scan(_name_,-1,p,l,'_');

924 length Variable $32 Statistic $8;

925 Variable = substr(_name_,1,p-2);

926 Statistic = substr(_name_,p);

927 run;

NOTE: There were 120000 observations read from the data set WORK.STATS1.

NOTE: The data set WORK.STATS1 has 120000 observations and 8 variables.

NOTE: DATA statement used (Total process time):

real time 0.10 seconds

user cpu time 0.04 seconds

system cpu time 0.03 seconds

Memory 209k

OS Memory 13424k

Timestamp 6/1/2012 8:42:34 PM

928 proc sort data=stats1 sortseq=LINGUISTIC(NUMERIC_COLLATION=ON);

929 by Variable;

930 run;

NOTE: There were 120000 observations read from the data set WORK.STATS1.

NOTE: The data set WORK.STATS1 has 120000 observations and 8 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 0.79 seconds

user cpu time 0.24 seconds

system cpu time 0.14 seconds

Memory 61002k

OS Memory 73628k

Timestamp 6/1/2012 8:42:35 PM

931 proc transpose data=stats1 out=stats2(drop=_name_);

932 by Variable;

933 var col1;

934 id Statistic;

935 idlabel Statistic;

936 run;

NOTE: There were 120000 observations read from the data set WORK.STATS1.

NOTE: The data set WORK.STATS2 has 20000 observations and 7 variables.

NOTE: PROCEDURE TRANSPOSE used (Total process time):

real time 0.32 seconds

user cpu time 0.31 seconds

system cpu time 0.01 seconds

Memory 2197k

OS Memory 15476k

Timestamp 6/1/2012 8:42:35 PM

937 %let end = %sysfunc(datetime());

938 %put NOTE: START=&start END=&end;

NOTE: START=1654202521.463 END=1654202555.409

939 %put NOTE: ET = %sysfunc(sum(-&start,&end),time12.);

NOTE: ET = 0:00:34

Re: Summary stats

Ksharp — Mon, 04 Jun 2012 04:51:45 GMT

NULL,

I know what your code means, I still don't think your code is a good one.

You force me to recode it for your intention. The only think I need is the number of numeric variables in this dataset.

Check it out. I think it is better and faster than yours.

data heart;set sashelp.heart;run;





proc summary data=heart nway;
   class sex;
   var _numeric_;
   output out=Stats0(drop=_:) n= nmiss= min= max= mean= median= / autoname;
run;
proc sql noprint;
 select num_numeric into : n
  from dictionary.tables
   where libname='WORK' and memname='HEART';
quit;
data want;
 set Stats0;
 length vname $ 40 ;
 array _a{*} _numeric_ ;
 do i=1 to &n ;
  vname=scan(vname(_a{i}),1,'_');
  n=_a{i} ;
  nmiss=_a{i+&n } ;
  min=_a{i+2*&n } ;
  max=_a{i+3*&n } ;
  mean=_a{i+4*&n } ;
  median=_a{i+5*&n } ;
  output;
 end;
 keep sex vname n nmiss min max mean median ;
run;

Ksharp

Re: Summary stats

data_null__ — Mon, 04 Jun 2012 16:06:56 GMT

I was comparing your loopy program to the more logical approach of summarizing all the variables in one procedure. You appear to understand the importance of that now, as you have abandoned that approach and have chosen to focus on PROC TRANSPOSE.

The transpose of the very wide data from PROC SUMMARY is not the limiting factor in this problem. You claim your data step transpose is faster than PROC TRANSPOSE may be true but did you test it and how much slower would it need to be to make a difference.

I would argue that any performance gain that might (as yet unproven) be had with the data step transpose is outweighed by the inflexibility of data step you wrote. What happens when you add a statistic? You have to rewrite the data step transpose.