DATA Step, Macro, Functions and more

Count a value in percentages but not means for survey items

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

Count a value in percentages but not means for survey items

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;

 


Accepted Solutions
Solution
‎09-23-2016 04:41 PM
Super User
Posts: 19,855

Re: Count a value in percentages but not means for survey items

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


All Replies
Solution
‎09-23-2016 04:41 PM
Super User
Posts: 19,855

Re: Count a value in percentages but not means for survey items

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.

Contributor
Posts: 23

Re: Count a value in percentages but not means for survey items

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. 

 

 

Contributor
Posts: 23

Re: Count a value in percentages but not means for survey items

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

Super User
Posts: 11,343

Re: Count a value in percentages but not means for survey items

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?

Contributor
Posts: 23

Re: Count a value in percentages but not means for survey items

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

Super User
Posts: 10,044

Re: Count a value in percentages but not means for survey items

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;

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 397 views
  • 1 like
  • 4 in conversation