hi,
I'm trying to generate the following table with proc tabulate, but it looks more challenging than I expected. is it possible to generate it using proc tabulate? what is the most efficient way?
Region | # of Completed Questionnaire | Employees # | Return rate % |
ATLANTIC | 3744 | 4066 | 92.1% |
HQ | 10824 | 11876 | 91.1% |
ONTARIO | 10732 | 12490 | 85.9% |
PACIFIC | 4212 | 4649 | 90.6% |
PRAIRIES | 5449 | 6722 | 81.1% |
QUEBEC | 4704 | 5361 | 87.7% |
Grand Total | 39,665 | 45,164 | 87.8%
|
It partly depends on how your data is structured but you should be able to do that in PROC TABULATE. Not sure what you mean about "approach", its a pretty straightforward table.
For return rate, if you code that variable as 0/1 the Mean would be the percents you want.
If your unfamiliar with PROC TABULATE spend an hour or two reading papers on lexjansen.com and then try it yourself.
@NazaninSAS wrote:
hi,
I'm trying to generate the following table with proc tabulate, but it looks more challenging than I expected. is it possible to generate it using proc tabulate? what is the most efficient way?
Region # of Completed Questionnaire Employees # Return rate % ATLANTIC 3744 4066 92.1% HQ 10824 11876 91.1% ONTARIO 10732 12490 85.9% PACIFIC 4212 4649 90.6% PRAIRIES 5449 6722 81.1% QUEBEC 4704 5361 87.7% Grand Total 39,665 45,164 87.8%
@NazaninSAS wrote:
hi,
I'm trying to generate the following table with proc tabulate, but it looks more challenging than I expected. is it possible to generate it using proc tabulate? what is the most efficient way?
Region # of Completed Questionnaire Employees # Return rate % ATLANTIC 3744 4066 92.1% HQ 10824 11876 91.1% ONTARIO 10732 12490 85.9% PACIFIC 4212 4649 90.6% PRAIRIES 5449 6722 81.1% QUEBEC 4704 5361 87.7% Grand Total 39,665 45,164 87.8%
Example of your starting data really helps. This is quite possible. One way involves having a Region variable and a variable that indicates completed with a value of 1 or 0 otherwise for each employee.
data have; do region = 'Atlantic','HQ','Ontario'; employees = floor(1000*rand('uniform')); completionrate = round(rand('uniform'),0.001); do i=1 to employees; completed=rand('uniform') le completionrate; output; end; end; keep region completed; run; proc tabulate data=have; class region; var completed; table region=' ' All='Grand Total', completed=' '*(sum='# completed'*f=best5. n='Number of employees' mean='Return Rate'*f=percent7.1) /box='Region' ; run;
But where your data starts there may be other manipulation of data to get a form that Tabulate (or Proc Report for that matter) likes.
The example data set is just to create and example data with the region variable and a random number of completed/not completed survey indicators.
Thanks,
why did you use "Mean"?
my third column is first column/second column.
I also tried SAS Enterprise, but I receive errors when I try to change the code for calculation of third column.
regards,
Nazanin
@NazaninSAS wrote:
Thanks,
why did you use "Mean"?
my third column is first column/second column.
As I mentioned, the actual content of the data is critical. In my example I have a 1/0 coded value for each employee in the region.
Mean of any numeric is sum/ number of records. So don't think "first column/second column", think number of completed surveys/ number of attempted surveys (or employees). Then the relation of the mean of a 1/0 coded variable to percent is obvious.
If you must use the idea of columns then look into Proc Report. You can explicitly reference column results in code. This may be the way to go if you have variables that represent some sort of count of surveys and employees already. But since you have not provided any example of the existing data that you have I can't make any specific suggestions.
Thanks,
do you know what will be the equivalent to "proc report" in Enterprise guide?
I am able to get the desired output by using proc sql, but SAS folks tell me that the proc report is more efficient.
this is my code using proc sql:
proc sql;
select REGION, count(SI_PRI) as SI_PRI, count (PRI)as PRI,
calculated SI_PRI/calculated PRI "Return rate" format=percent8.1
from work.Final2
group by REGION
union all
select 'TOTAL', count(SI_PRI) as SI_PRI, count (PRI)as PRI,
calculated SI_PRI/calculated PRI "Return rate" format=percent8.1
from work.Final2
;
Quit;
regards,
Nazanin
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: