BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
TanviG
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
SuryaKiran
Meteorite | Level 14

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

8 REPLIES 8
novinosrin
Tourmaline | Level 20

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
Fluorite | Level 6

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
Meteorite | Level 14

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
TanviG
Fluorite | Level 6
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
Fluorite | Level 6
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
Fluorite | Level 6
Any more feasible and time efficient solution is welcome!
SuryaKiran
Meteorite | Level 14
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
ballardw
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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