BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
subhrajitcet0
Fluorite | Level 6

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?

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

View solution in original post

9 REPLIES 9
PaigeMiller
Diamond | Level 26

Explain what "summary values" you are talking about.

 

Show us your PROC SQL code.

--
Paige Miller
subhrajitcet0
Fluorite | Level 6

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
Tourmaline | Level 20

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
Diamond | Level 26 RW9
Diamond | Level 26

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

proc freq data=<your dataset>;
  tables _all_ / nocum nopercent;
run;
subhrajitcet0
Fluorite | Level 6

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:

VariableLabelNNmiss
MSRP 4280
Invoice 4280
EngineSizeEngine Size (L)4280
Cylinders 4262
Horsepower4280
MPG_CityMPG (City)4280
MPG_HighwayMPG (Highway)4280
WeightWeight (LBS)4280
WheelbaseWheelbase (IN)4280
LengthLength (IN)4280

 

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
Diamond | Level 26

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.

--
Paige Miller
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Fluorite | Level 6

That's perfect RW9.

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

PeterClemmensen
Tourmaline | Level 20

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is ANOVA?

ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 9 replies
  • 3121 views
  • 2 likes
  • 4 in conversation