I have a dataset in which 'Actual.balance' is a numeric column. How do I get these below summary attributes for the variable 'Actual.Balance' printed in a column with their values adjacent to it.
ColName | variable | value |
Actual.Balance | Mean | 94590150 |
Actual.Balance | Median | 107436 |
Actual.Balance | SD | 1020714752 |
Actual.Balance | NegativeCount | 14 |
Actual.Balance | Lowest | -8742115 |
Actual.Balance | Highest | 18800512187 |
Actual.Balance | Two_SD_Away | 4 |
Actual.Balance | Two_SD_Below | 0 |
Actual.Balance | Three_SD_Away | 3 |
Actual.Balance | Three_SD_Below | 0 |
Any help would be really beneficial for me.
Hi RW9, thanks for the solution. I used PROC MEANS for the same on dummy table i.e. sashelp.cars for the 'Cylinders' variable. Please find code below:
ods exclude all;
proc means data=sashelp.cars Mean Median stddev min max stackodsoutput;
var Cylinders;
ods output summary=MeansSummary;
run;
ods exclude none;
proc transpose data=MeansSummary out=meanssummary_transposed(drop=_label_ rename=(_name_=Variable Col1=value));
run;
proc sql;
alter table meanssummary_transposed
add ColName char(30);
quit;
proc sql;
update meanssummary_transposed
set Colname='Cylinders';
quit;
But I am unable to get Summary statistics as Negative count, two_SD_away,two_SD_below,three_SD_Away,three_SD_below through PROC MEANS.
Could you please help?
I don't know what you mean by Negative count or one_SD_away etc.? As far as I can tell there is no such calculation, you get stddev, and if you need further processing you use that, i.e. 1 away would be = 1 * stddev, 2 would be 2 * stddev.
Also if you change yours means a bit you can avoid transposing:
proc means data=sashelp.cars; var Cylinders; output out=MeansSummary n=n mean=mean median=median stddev=stddev min=min max=max; run; data meansummary; length col1 $30; set meanssummary; col1="Cylinders"; run;
Actual.Balance | Mean | MEANS |
Actual.Balance | Median | MEANS |
Actual.Balance | SD | MEANS |
Actual.Balance | NegativeCount | FREQ + FORMAT |
Actual.Balance | Lowest | MEANS |
Actual.Balance | Highest | MEANS |
Actual.Balance | Two_SD_Away | Manual, format + FREQ or DATA STEP |
Actual.Balance | Two_SD_Below | Manual, format + FREQ or DATA STEP |
Actual.Balance | Three_SD_Away | same as above |
Actual.Balance | Three_SD_Below | same as above |
It depends on the statistics. See the table above for which procs will work for which metrics. This means you're calculating some things manually.
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.