topic Re: How do i get the summary statistics of a dataset for all variables column wise in a dataset in Statistical Procedures

How do i get the summary statistics of a dataset for all variables column wise in a dataset

subhrajitcet0 — Mon, 11 Dec 2017 13:18:14 GMT

How do I output the summary values of a dataset column wise as below?

ColName type n NAs NonNAs UniqueValues
SBL_BB.Loans. character 398 0 398 3
Unique.Id integer 398 0 398 NA
Scenario character 398 0 398 1
Entity character 398 0 398 4
Business character 398 0 398 9
Issuer Category character 398 0 398 8

I am trying to use Proc SQL for the same but am unable to understand how to get the 'type' of each variable using PROC SQL.

Can anyone please help?

Re: How do i get the summary statistics of a dataset for all variables column wise in a dataset

PaigeMiller — Mon, 11 Dec 2017 13:22:15 GMT

Explain what "summary values" you are talking about.

Show us your PROC SQL code.

Re: How do i get the summary statistics of a dataset for all variables column wise in a dataset

PeterClemmensen — Mon, 11 Dec 2017 13:34:39 GMT

The VTYPE function gives you the type of the variable.

However, it returns N or C (Numeric/Character) and does not return integer as this is not a variable type.

Re: How do i get the summary statistics of a dataset for all variables column wise in a dataset

subhrajitcet0 — Mon, 11 Dec 2017 13:39:59 GMT

Hello PaideMiller,

What i require is the variable type, no of non missing observations, no of missing observations and no of unique values for each variable in a dataset.

All the above options should be printed against each variable with the variable names in the first column.

Re: How do i get the summary statistics of a dataset for all variables column wise in a dataset

PeterClemmensen — Mon, 11 Dec 2017 13:42:38 GMT

What does your current PROC SQL code look like?

Again, the VTYPE function will return the type of the variable as you requested in your original post 🙂

Re: How do i get the summary statistics of a dataset for all variables column wise in a dataset

RW9 — Mon, 11 Dec 2017 13:45:46 GMT

Use proc freq, that is the purpose of the procedure:

proc freq data=<your dataset>;
  tables _all_ / nocum nopercent;
run;

Re: How do i get the summary statistics of a dataset for all variables column wise in a dataset

subhrajitcet0 — Mon, 11 Dec 2017 13:59:21 GMT

Thanks for your response RW9, but PROC FREQ gives me a detailed frequency of each variable's value rather than giving me a result on the different variables.

I have used the code:

ods exclude all;

proc means data=sashelp.cars N Nmiss stackodsoutput;

ods output summary=MeansSummary;

run;

ods exclude none;

I get the output as:

Variable	Label	N	Nmiss
MSRP		428	0
Invoice		428	0
EngineSize	Engine Size (L)	428	0
Cylinders		426	2
Horsepower	428	0
MPG_City	MPG (City)	428	0
MPG_Highway	MPG (Highway)	428	0
Weight	Weight (LBS)	428	0
Wheelbase	Wheelbase (IN)	428	0
Length	Length (IN)	428	0

Along with N and Nmiss I want the variable type and no of unique values for each Variable in the dataset. How do I get the variable type and no of unique values in PROC MEANS?

Re: How do i get the summary statistics of a dataset for all variables column wise in a dataset

PaigeMiller — Mon, 11 Dec 2017 14:33:39 GMT

Thanks for your response RW9, but PROC FREQ gives me a detailed frequency of each variable's value rather than giving me a result on the different variables.

Then it is a simple matter for you to count the number of values of each variable from the output of PROC FREQ; this can be done using the OUTPUT option of PROC FREQ, followed by another PROC MEANS or PROC FREQ.

Re: How do i get the summary statistics of a dataset for all variables column wise in a dataset

RW9 — Mon, 11 Dec 2017 14:39:52 GMT

Ah, bit more complex then, maybe something like:

proc sql;
  create table WANT (VAR char(20),TYPE char(20),N num,NMISS num,NUNIQUE num);
quit;

data _null_;
  set sashelp.vcolumn (where=(libname="SASHELP" and memname="CARS"));
  call execute(cat('proc sql; insert into WANT set VAR="',strip(name),'",TYPE="',strip(type),
                    '",NMISS=(select count(distinct ',strip(name),') from SASHELP.CARS where missing(',strip(name),')),
                     NUNIQUE=(select count(distinct ',strip(name),') from SASHELP.CARS); quit;'));
run;

proc sql;
  update WANT
  set N=(select NOBS from SASHELP.VTABLE where LIBNAME="SASHELP" and MEMNAME="CARS");
quit;

Re: How do i get the summary statistics of a dataset for all variables column wise in a dataset

subhrajitcet0 — Tue, 12 Dec 2017 08:06:56 GMT

That's perfect RW9.

This is the exact solution I wanted. Thanks a lot.