Desktop productivity for business analysts and programmers

summary problem

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

summary problem

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.


Accepted Solutions
Solution
a week ago
Valued Guide
Posts: 560

Re: summary problem

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;
Thanks,
Suryakiran

View solution in original post


All Replies
PROC Star
Posts: 1,584

Re: summary problem

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;
Occasional Contributor
Posts: 9

Re: summary problem

Posted in reply to novinosrin

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.

Solution
a week ago
Valued Guide
Posts: 560

Re: summary problem

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;
Thanks,
Suryakiran
Occasional Contributor
Posts: 9

Re: summary problem

Posted in reply to SuryaKiran
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?
Occasional Contributor
Posts: 9

Re: summary problem

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;

Occasional Contributor
Posts: 9

Re: summary problem

Any more feasible and time efficient solution is welcome!
Valued Guide
Posts: 560

Re: summary problem

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

Thanks,
Suryakiran
Super User
Posts: 13,328

Re: summary problem

[ Edited ]
Posted in reply to SuryaKiran

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 178 views
  • 3 likes
  • 4 in conversation