Proc Report : How do you create a single report showing frequencies and % for multiple variables

[bananah13]

*Update with SAS sample data set. Cannot link to github on my computer.

Hello,

How do I create a proc report that shows multiple variables and the sum of their values by the grouped variable 'agency'? The below code creates the pasted table, but it does not show n values of proceeding variable, rather it shows the n value of addressx across all of the n columns (note how the all have the same value sums). Except for agency, each variable has 2 values (1 and 2). They are all formatted 1=Missing or Unknown ; 2=Complete. I would prefer not to have to make a separate report for each variable. 

Second, I would like the % column to show the total of the variable by agency rather than the column totals, which is what is showed now. For examples, For the variable 'First Name', % column under the row 'starlight' would show: 83.3% (749/901) for 'complete' and 16.8% (152/901) for 'missing or unknown' rows. Also written as: [first name 'complete' by agency' (numerator)/first name 'total' by agency (denominator)] for row with value 'complete'.  [First name 'missing or unknown' by agency' (numerator)/first name 'total' by agency (denominator)] for row with value 'missing or unknown'.  

Code:

proc report data=QC.LEOClient_NewVar&SYSDATE missing ;
  column agency (firstnameX n pctn) (lastnameX n  pctn ) (addressx n pctn) ;

    define agency/group format=agencyf. "Agency";
    define firstnameX / group format=firstnamex. "First Name" ;

    define lastnameX / group format=lastnamex. "Last Name" ;

    define addressx / group format=addressx. "Address" ;

    define pctn / "%" f=percent10.3;

run;

Output:

Agency	First Name	n	%	Last Name	n	%	Address	n	%
Starlight	Complete	749	7.912%	Complete	749	7.912%	Complete	749	7.912%
		152	1.606%		152	1.606%	Missing or Unknown	152	1.606%
	Missing or Declined	42	0.444%	Missing or Declined	42	0.444%	Missing or Unknown	42	0.444%
Sunshine	Complete	127	1.342%	Complete	127	1.342%	Complete	127	1.342%
		4	0.042%		4	0.042%	Missing or Unknown	4	0.042%
	Missing or Declined	2	0.021%	Missing or Declined	2	0.021%	Missing or Unknown	2	0.021%
Moonbeam	Complete	42	0.444%	Complete	42	0.444%	Complete	42	0.444%
		3	0.032%		3	0.032%	Missing or Unknown	3	0.032%
	Missing or Declined	1	0.011%	Missing or Declined	1	0.011%	Missing or Unknown	1	0.011%

[ballardw]

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software or policies. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

When discussing percentages you need to be very clear what the numerator and denominator are and where they come from. I'm not sure that I actually follow your description of what percentages you want.

[Cynthia_sas]

Hi, and, to add to what @ballardw suggested, even if somebody was kind enough attempt to make some fake data, since you did not provide the user-defined formats, or specify the ODS destination, they could not run your code.

  It almost seems to me that you want TABULATE and not REPORT for this anyway because with TABULATE, you have more percent statistics than you have available with REPORT.

Cynthia