topic Re: summary problem in SAS Enterprise Guide

summary problem

TanviG — Tue, 12 Jun 2018 19:22:13 GMT

Hi, I am doing summary for each columns and want to know missing and non-missing percentage in the data. I have to classify it on country level.

Ideally, I have to calculate percentage of missing and non-missing sale (another variable in my data) on country level. Can anyone help please.

data test;

set sashelp.prdsal2;

sale=0; 

if actual> predict then sale=.; run;

proc summary data=test;

class country;

var sale;

run;

Thanks.

Re: summary problem

novinosrin — Tue, 12 Jun 2018 19:29:17 GMT

Do you mean you are after this?

data test;

set sashelp.prdsal2;

sale=0; 

if actual> predict then sale=.; run;



proc freq data=test noprint;
tables country*sale/missing out=want;
run;

Re: summary problem

TanviG — Tue, 12 Jun 2018 19:38:37 GMT

No, as per my problem I want to calculate percentage within each country assuming each country has 100% population and within country missing and non-missing percentage.

Re: summary problem

SuryaKiran — Tue, 12 Jun 2018 19:48:17 GMT

You might need PROC TABULATE

PROC TABULATE DATA=WORK.TEST;
	CLASS COUNTRY /	ORDER=UNFORMATTED MISSING;
	CLASS sale /	ORDER=UNFORMATTED MISSING ;
	TABLE COUNTRY,sale*RowPctN ;
RUN;

Re: summary problem

TanviG — Tue, 12 Jun 2018 20:00:59 GMT

Thanks. It worked! I am assuming if there are more than one variable and if for each variable I need to calculate the missing percentage..how it will work? Do I need to write table statement again? and if I am taking it into the output data set then can I write out= within the proc statement?

Re: summary problem

TanviG — Tue, 12 Jun 2018 20:07:56 GMT

PROC TABULATE DATA=WORK.TEST out=best (drop=_type_  _page_ _table_);
	CLASS COUNTRY /	ORDER=UNFORMATTED MISSING;
	CLASS sale /	ORDER=UNFORMATTED MISSING ;
	class resale/ ORDER=UNFORMATTED MISSING ;
	TABLE COUNTRY,sale*RowPctN ;
	table COUNTRY,resale* RowPctN;
RUN;

Re: summary problem

TanviG — Tue, 12 Jun 2018 20:20:38 GMT

Any more feasible and time efficient solution is welcome!

Re: summary problem

SuryaKiran — Tue, 12 Jun 2018 20:43:22 GMT

PROC TABULATE DATA=WORK.TEST;
	CLASS COUNTRY /	ORDER=UNFORMATTED MISSING;
	CLASS resale /	ORDER=UNFORMATTED MISSING;
	CLASS sale /	ORDER=UNFORMATTED MISSING ;
	TABLE /* Row */ COUNTRY,
		  /* Col */ sale*RowPctN resale*RowPctN ;
RUN;

Check how row's and Columns are controlled

Re: summary problem

ballardw — Wed, 13 Jun 2018 19:49:09 GMT

@SuryaKiran wrote:

PROC TABULATE DATA=WORK.TEST;
	CLASS COUNTRY /	ORDER=UNFORMATTED MISSING;
	CLASS resale /	ORDER=UNFORMATTED MISSING;
	CLASS sale /	ORDER=UNFORMATTED MISSING ;
	TABLE /* Row */ COUNTRY,
		  /* Col */ sale*RowPctN resale*RowPctN ;
RUN;

Check how row's and Columns are controlled

Or for variables that you are doing the same things for group with () and you can have multiple variables on a single CLASS statement as long as all options are the same

PROC TABULATE DATA=WORK.TEST;
	CLASS COUNTRY /	ORDER=UNFORMATTED MISSING;
	CLASS resale sale /	ORDER=UNFORMATTED MISSING;
	TABLE  COUNTRY,
		   (sale resale)*RowPctN ;
RUN;

Proc tabulate will also honor variable lists such as QR:, var1-var9 or ThisVar--ThatVar to use all variables in the list in Class and Var statements as well as in the Table definition.

You can also use the special variable lists _numeric_ or _character_ .