topic Re: Proc tabulate in SAS Programming

Proc tabulate

NazaninSAS — Wed, 05 Dec 2018 19:27:42 GMT

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%

Re: Proc tabulate

Reeza — Wed, 05 Dec 2018 20:00:49 GMT

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%

Re: Proc tabulate

ballardw — Wed, 05 Dec 2018 23:23:30 GMT

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

Re: Proc tabulate

NazaninSAS — Thu, 06 Dec 2018 14:03:57 GMT

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

Re: Proc tabulate

ballardw — Thu, 06 Dec 2018 17:24:52 GMT

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

Re: Proc tabulate

NazaninSAS — Thu, 06 Dec 2018 17:31:14 GMT

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