topic Re: proc report in SAS Procedures

proc report

tmm — Mon, 04 Feb 2013 15:26:46 GMT

I am having proc report issues and not sure why.

I have a table I am trying to do a proc report output html and it is just not working. The column in question is pri_drg_count and it is a number type with 8 in length. My code is:

ods html file='my path';

proc report nowd data=pci.ipall split='/';

title'Overall PCI';

columns maj_mkt_nm pci_drg_count mbr_count;

define maj_mkt_nm/group 'Market' missing;

define pci_drg_count/group order=freq descending 'PCI Count' center;

define mbr_count/no print

define perc1/computed format=percent10.2 'PCI Percent' center missing;

compute perc1;

perc1=_c2_/_c3_;

endcomp;

run;

ods html close;

The out put everytime is:

market pci_count percent

alabama 12 %

georgia 400 %

texas 300 %

nyc 500 %

i need it to be

market pci_count percent

nyc 500 %

georgia 400 %

texas 300 %

alabama 12 %

PLEASE NOTE, THE CODE RUNS FINE, NO ERRORS, THE % ACTUALLY DISPLAYS THE %, IT IS JUST I HAVE THIS CODE ON WORK COMPUTER AND TYPING FROM LOOKING AT THE SCREEN ONTO MY PERSONAL COMPUTER SCREEN SO IF I MISSED SOMETHING MY QUESTION IS NOT WHAT I AM MISSING IN THE CODE OR TYPOS. MY PROBLEM IS SOLELY I CANNOT GET THE PCI_COUNT TO DESCEND.

Re: proc report

ballardw — Mon, 04 Feb 2013 15:37:47 GMT

I think you are getting the order you want WITHIN the market as market is the first column. Try switching the order of market and PCI.

Re: proc report

tmm — Mon, 04 Feb 2013 15:40:18 GMT

I can switch them but then how would I them make Market appear in the first column?

Re: proc report

Data_Detective_23219 — Mon, 04 Feb 2013 15:43:45 GMT

Get rid of setting up maj_mkt_nm as a GROUP variable.

Re: proc report

tmm — Mon, 04 Feb 2013 15:44:50 GMT

Ugh. That did not work. I might just have to not bother with it as I have been trying to figure this out all weekend and I really need to get on with the rest of the code. I might just throw it into Excel and then descend in the sheet because I have researched so many books and sites and cannot find an answer. Just spending far too much time on this.

Re: proc report

Data_Detective_23219 — Mon, 04 Feb 2013 15:46:57 GMT

Try then changing

define pci_drg_count/group order=freq descending 'PCI Count' center;

define pci_drg_count/order order=freq descending 'PCI Count' center;

Re: proc report

tmm — Mon, 04 Feb 2013 16:10:18 GMT

Thanks, but I tried that earlier and it did not work. Just tried it again and it does not work. I am on SAS 9.2 so perhaps there is a different format I need. I am just going on to the rest of the code and if I figure it out sometime I will post what I had to do. Otherwise I will just export to excel and sort. Thanks.

Re: proc report

Cynthia_sas — Wed, 06 Feb 2013 03:04:23 GMT

Hi:

It really depends on whether your data are already summarized or not. If pci_drg_count is already a summary number, then you can use it to order your rows. It would have to appear before maj_mkt_nm, though, because, ordering happens from left to right on the report row, so once ALABAMA is the first row, you have no opportunity to "reorder" based on pci_drg_count. The code below shows you how to put pci_drg_first for ordering (just use ORDER DESCENDING) and then once you understand how it's working, you can use NOPRINT on the first column.

There is no need to use ABSOLUTE column numbers such as _C2_ and _C3_ for this report, because you don't have any across variables, so using the report item names will work just fine. For mbr_count, the usage is SUM, so you have to use the variable.statistic reference method in the COMPUTE block.

The report item PDC is an alias for PCI_DRG_COUNT .. you use the "regular" variable name to control ordering and then you make an alias for that variable so you can refer to it by the simple name in the COMPUTE block and so that it can still appear "after" maj_mkt_nm, essentially being used twice on the report.

If the data are NOT already summarized, then you would have to use a different technique. But since you did not show any of the original data and since you used PCI_DRG_COUNT as GROUP, implying that it was already summarized, I just made some summarized "fake" data.

cynthia

*make some fake, pre-summarized data;

data ipall;
infile datalines;
input maj_mkt_nm $ pci_drg_count mbr_count;
return;
datalines;
alabama   12   24
georgia 400 800
texas    300 450
nyc      500 750
;
run;

ods listing close;
ods html file='c:\temp\order_desc.html';

proc report nowd data=ipall split='/' nowd;

title'Overall PCI';
columns pci_drg_count maj_mkt_nm pci_drg_count=pdc mbr_count perc1;
define pci_drg_count / order descending 'PCI for ordering' /* noprint */;
define maj_mkt_nm/order 'Market' missing;
define mbr_count/ analysis sum /* noprint */;
define pdc / order 'PCI Count';
define perc1/computed format=percent10.2 'PCI Percent' center missing;
compute perc1;
** use column names instead of absolute numbers;
** since you pci_drg_count for noprint;
perc1=pdc/mbr_count.sum;
endcomp;
run;

ods html close;