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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.