summary problem

[TanviG]

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.

[novinosrin]

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;

[TanviG]

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.

[SuryaKiran · Accepted solution]

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;

[TanviG]

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?

[TanviG]

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;

[TanviG]

Any more feasible and time efficient solution is welcome!

[SuryaKiran]

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

[ballardw]

---

@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_ .