topic Re: Proc means in SAS Procedures

Proc means

Siddharth123 — Sun, 07 Sep 2014 15:07:05 GMT

Hi All,

I have a dataset in SASHelp library- citiday. What I want to do is to sumamrise the number of missing values for each numeric variable in this dataset and have the results for each numeric variable with percentage of missing in a dataset Full. I tried the code below, but cant put things together.

proc contents data=sashelp.citiday noprint out=_temp; run;

proc means data=sashelp.citiday n nmiss;

var _numeric_;

ods output summary=_stat_tem(drop=label: );

run;

proc sql;

create table full as

select a.*,a.NMiss /(a.N+a.NMiss) * 100 as pct_missing, b.label from _stat_tem (rename=(variable=varname)) a

inner join _temp b

on a.varname=b.name;

quit;

Please help/advise.

Thnak you

Re: Proc means

Reeza — Sun, 07 Sep 2014 17:37:20 GMT

I have a macro that does that if you're interested:

SAS - Missing Macro - For a dataset, variable level report the number of missing and non-missing observatins

Re: Proc means

stat_sas — Sun, 07 Sep 2014 18:58:10 GMT

ods output summary=summary;
proc means data=sashelp.class stackods n nmiss;
var _numeric_;
run;

data want;
set summary;
pct_missing=nmiss/(n+nmiss);
format pct_missing percent6.2;
run;

proc print data=want;
run;

Re: Proc means

Siddharth123 — Mon, 08 Sep 2014 06:44:16 GMT

Thank you Reeza and stat@sas.

My nly problem in using the macro suggested by Reeza is that this macro given percentage of missing for each variable in the dataset. What I am also looking for is to add summary information , for each variable, like - 1st percentile, 5th percentile, minimum, maximum etc. ..

This can be only achieved by proc means. Now, when I use proc means -

	proc means data=&libname..&dsetin. mean std min max p1 p5 p10 p25 p50 p75 p90 p95 p99;
	var _numeric_;
	ods output summary=_stat_&var.;

run;

How do I merge with the &dsetout output to get percent missing for all variables in One file. This is because when I use _numeric_ in Proc means, I could not retain the name of my original variable so could not merge it back with the dataset having missing percent information.

Kind regards

Re: Proc means

Ksharp — Mon, 08 Sep 2014 12:53:32 GMT

ods output summary=_stat_&var. /autoname ;

Re: Proc means

Reeza — Mon, 08 Sep 2014 18:38:36 GMT

If you need other stats and have only numeric variables, I suggest using @stat@sas solution instead.

Simply add the variables into the code and it will generate the numbers required in less steps.

Re: Proc means

Siddharth123 — Mon, 08 Sep 2014 21:48:47 GMT

hello Stat@stat,

thank you for the help. Is there an option in SAS which puts the percntile information in the _FREQ_ column? I mean when I use -

proc means data= SASHelp.Citiday n nmiss mean std min max p1 p5 p10 p25 p50 p75 p90 p95 p99;

var _numeric_;

ods output =test (drop=label:)

;

run;

This doesnt produce pernctile information (only - N, NMISS, MAX,MIN,MEAN,STD).

But if I use -

proc means data= SASHelp.Citiday n nmiss mean std min max p1 p5 p10 p25 p50 p75 p90 p95 p99;

var _numeric_;

ods output summary=_stat_temppretty(drop=label:)

;

run;

The problem is that even if I do get percentile the summary information gets added as a suffix to the variable name. For example, if I have a variable AMOUNT, I get AMOUNT_N, AMOUNT_NMISS, AMOUNT_P1....so on. So I cant even use a proc transpose here. Basically, my end goal is to have all missing data, percntile, min, max, median for original variables.

@ksharp - the autoname does not work here.

Re: Proc means

Reeza — Mon, 08 Sep 2014 21:54:25 GMT

You didn't use the code posted by Stat@sas. Note the STACKODS option used, which is missing from your code.

See the example from here:

proc means data=sashelp.class stackods n nmiss p1 p5 p95 p99;

var _numeric_;

ods output summary=want;

run;