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

What are some strategies for getting tables of percentages *and* means for survey items? 

 

Say for example I am measuring self-reported job skills. Respondents can answer that their skills for three different items are Weak (0), Fair (1), Good (2), Very good (3), Excellent (4), or that they have never done that task, or they can skip the question. I want to show both the percentages of each of those categories *and* the means 

 

testtt.JPG

 

I have been using proc tabulate to calculate the percentages for the scale items. However, if respondents skip an item then they are excluded from the entire procedure, even though their answers should be included for non-skipped items. And then there's the question of means--If I code "I have not done this" as 9 then it will mess up the means. 

 

I have altered a macro that I found here to create the percentage tables by appending each freq output. Is there some way to include the "I have not done this" in that macro that won't mess up the means? 

 

Here's the Macro: 

 

 

%macro summary_table(dsetin, varlist, dsetout);

	*delete the previous version of the output dataset; 
	proc datasets nodetails nolist;
	    delete &dsetout;
	quit;

	*loop through variable list;
	%let i=1;
	%do %while (%scan(&varlist, &i, " ") ^=%str());
	%let var=%scan(&varlist, &i, " ");   
	%put &i &var; * This is a "Macro Variable" which can store a value without a dataset; 

		*Cross tab;
	    proc freq data=&dsetin;
	    table &var/ out=temp1 noprint;
	    run;
	    *Get variable label as name;
	    data _null_;
	        set &dsetin (obs=1);
	        call symput('var_name', vlabel(&var.));
	    run;
	    %put &var_name;
	    *Add in Variable name and store the levels as a text field;
	    data temp2;
			keep variable value count percent;
			length variable $200.; 
	        Variable = "&var_name";
	        set temp1;
	        value=input(&var, &format.);
	        percent=percent/100; * store these as decimals instead of numbers;
	        format percent percent8.1;
	        drop &var.;
	    run;
	    %put &var_name;
	    *Append datasets;
	    proc append data=temp2 base=&dsetout force;
	    run;
	    *drop temp tables so theres no accidents; 
	    proc datasets nodetails nolist;
	        delete temp1 temp2;
	    quit;
	*Increment counter;
	%let i=%eval(&i+1);
	%end;
%mend;

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

Glad to see my code is useful, but I wouldn't actually recommend it in this situation. Also, there's a newer version that I think is better:

https://gist.github.com/statgeek/e0903d269d4a71316a4e

 

I wouldn't recommend coding I have not done this to a 9. 

I'm assuming that this value is not the same as missing?

 

If you post some sample data I can help with a different method.

View solution in original post

6 REPLIES 6
Reeza
Super User

Glad to see my code is useful, but I wouldn't actually recommend it in this situation. Also, there's a newer version that I think is better:

https://gist.github.com/statgeek/e0903d269d4a71316a4e

 

I wouldn't recommend coding I have not done this to a 9. 

I'm assuming that this value is not the same as missing?

 

If you post some sample data I can help with a different method.

shailey
Obsidian | Level 7

Sure, here's sample code: 

 

data example; 
	input repondent $ comm1 comm2 comm3; 
	datalines; 
	R1 1 3 4
	R2 . 0 3
	R, 1 . 2
	R4 . . 4 
	R5 1 3 4
	R6 9 0 . 
	; 
run; 

It should be missing when calculating means so that it is not factored into the mean. But it should not be missing when counted as a response category. 

 

 

shailey
Obsidian | Level 7

Thanks, this method works great. And WOW, VVALUEX is amazing! Thank you for introducing me to all of the V / VX functions. 

ballardw
Super User

With proc tabulate I would create a second variable with the same value as the scale (less the "haven't done this", set those missing) and use that a VAR varaible to get the mean while using the the original as a Class varaible for the percentages if that is what you are doing now.

 

Have you looked at ROWPCTN for the class variable?

shailey
Obsidian | Level 7

Thanks, this is basically what I'm doing now. Still wondering if there are other options, but maybe this is just the easiest. 

Ksharp
Super User
It is good for SQL.


data example; 
	input repondent $ comm1 comm2 comm3; 
	datalines; 
	R1 1 3 4
	R2 . 0 3
	R, 1 . 2
	R4 . . 4 
	R5 1 3 4
	R6 9 0 . 
	; 
run; 
data have;
set example;
comm=comm1;output;
comm=comm2;output;
comm=comm3;output;
drop comm1-comm3;
run;
proc sql;
select repondent,
sum(comm=0)/(select count(*) from have where repondent=a.repondent) as week,
sum(comm=1)/(select count(*) from have where repondent=a.repondent) as fair,
mean(comm) as mean
 from have as a
  group by repondent;
quit;

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 6 replies
  • 1390 views
  • 1 like
  • 4 in conversation