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

[subhrajitcet0]

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?

[PaigeMiller]

Explain what "summary values" you are talking about.

Show us your PROC SQL code.

[subhrajitcet0]

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.

[PeterClemmensen]

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 🙂

[RW9]

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

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

[subhrajitcet0]

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?

[PaigeMiller]

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.

[RW9 · Accepted solution]

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;

[subhrajitcet0]

That's perfect RW9.

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

[PeterClemmensen]

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.