I have a dataset 'test' as
var1 var2 var3 var4....varn
abcd M 22 1500...
qwerty M 32 5000...
asdf F 21 100...
tyuif F 33 6500..
jklmn M 45 800..
fghtn F 18 4000..
fgtn F 28 4050..
abcd M 22 1500..
abcde M 24 1500..
I need to get the output as
for only numeric variables
Header 1 | Header 2 | Header 3 | Header 4 | Header 5 | |
---|---|---|---|---|---|
Variable | Min | Max | Mean | Std | |
Var3 | |||||
Var4 | |||||
... | |||||
Varn |
for only character variables
Header 1 | Header 2 | Header 3 | Header 4 | |
---|---|---|---|---|
Variable | Mode | Freqency of Mode | No:of distinct varaibles | |
var1 | ||||
var2 | ||||
.. | ||||
varn |
The first part can be done using proc means.
Please help to solve the 2nd part
Have you taken a look at PROC TRANSPOSE?
no i havent tried.
But whether proc transpose will give me the mode , freq of mode and distinct count?
any clue?
This should work, I have solved for the numeric variables and left the char one as an exercise.
options symbolgen mlogic mprint; /* for debugginf macros, but the good stuff into the log */
data test; /* example dataset */
input string $ 1-4 gender $6 age 8-9 stat 10-14;
datalines;
abcd M 22 1500
qwer M 32 5000
asdf F 21 100
tyui F 33 6500
jklm M 45 800
fght F 18 4000
fgtn F 28 4050
;
data nums; /*subset numeric*/
set test(keep=_numeric_);
run;
data chars;/*subset character*/
set test(keep=_character_);
run;
/*get list of numeric variable names, shove them into a macro */
proc transpose data=nums out=numst;
run;
proc sql;
select _NAME_ into :numvars seperated by "*" from numst;
quit;
%let cardnumvars= %sysfunc(countw(&numvars.,*));
%put numvars.=&numvars;
%put cardnumvars.=&cardnumvars.;
%macro numstats;
proc sql;
/*for each numeric variable create table with the stats */
%do i=1 %to &cardnumvars.;
%let var = %scan(&numvars.,&i.);
create table num&i. as
select
"&var " as variable, /* hacking to stop truncation of name variable, needs better fix for general use */
min(&var) as min,
max(&var) as max,
avg(&var) as mean,
std(&var) as std
from nums
;
%end;
quit;
%mend numstats;
%numstats
/*put all the small stats tables together */
%macro lister;
%do i=1 %to &cardnumvars.;
num&i.
%end;
%mend lister;
data numstats;
set %lister ;
run;
/* print the output */
proc print data=numstats;
run;
/* similar proccess can be used for character variables, left as exercise */
Here's one way to get the Frequency of Mode and Number of Values for a single variable. There are other methods, such as SQL. I leave it up to you to get them into your report properly.
proc freq data=have order=freq;
tables var1 / noprint out=var1;
run;
data want;
set var1 (obs=1) nobs=_nobs_;
mode = var1;
frequency_of_mode = count;
number_of_distinct_values = _nobs_;
run;
Also note that PROC MEANS can compute the MODE. However, you may have to make a decision about what to report if two values are tied for the highest frequency.
Good luck.
Thank you Astounding and Murray_Court
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.