proc report grouping problem

[tmm]

I am exporting my report and it is not combining my fields like I would expect it to. The tables I am pulling are correct in that they will say:

memberid        admitdt         count         state

1111                 1/1/2010            1                     ny

1111                 1/2/2010             1                    ny

1111                 1/2/2010            2                     ny

the problem is when grouping in a report say the above it groups like this on the report

memberid            admitdt            count            state

1111                     1/1/2010              1                         ny

                              1/2/2010               3    

I do not want any null items but if I use display after the column names it is totally funky. My code is like this

columns     memberid state admitdt frequency

define memberid/group 'member';

define state/group 'state';

define admitdt/group 'date';

define count/analysis sum 'frequency' center;

The output is not giving me the memberid and the state code in each column as you see above. This makes it so I have to manually go

thru my execl file and copy the memberid and state to the blank columns. I spent 3 hours yesterday trying to figure out the issue by playing

with different report functions and nothing worked. I could have spent that 3 hours just manually fixing the about 500 rows that came over like

this and been off at 6pm instead of getting off at 9pm. I have to run this every quarter and if I have to manually do it fine. I just do not want to spend 3 hours again researching why it does not work. Waste of time. If someone knows how I can fix this that is great. If not I am prepared to fix the 500 some odd rows that are blank

every quarter

[Astounding]

If you plan on dumping the report into Excel, is there any reason you are locked into using PROC REPORT?

This would be a relatively simple problem if you could switch to:

proc means data=mydata sum;

   class memberid state admitdt;   /* or perhaps memberid admitdt state, your choice */

   var count;

   format admitdt mmddyy10.;   /* if it is actually a SAS date not a character string */

run;

You might have to make a few changes to the column order and column headings, but that's a lot less work than you're doing now.

[tmm]

Sorry, did I say Excel. I meant HTML. The group that wants this report wants it in an HTML format. The original code was not created by me but by my manager and this is how she and the group want the output to be. So they have coded proc report and output to HTML and that is how they want it. If it cannot be accomplished via the define area then I will just have to manual fix the output every quarter. FYI the code output does not have any issues when bouncing it against the DB2 server. It only does this with the data in the SQL Server.

[Astounding]

If you really must use PROC REPORT, there are other posters better qualified than myself to advise you.

Note, however, that PROC MEANS can also write in an HTML format.  I don't know how much flexibility there would be on the part of your manager or the group receiving the output.  But if you don't get a satisfactory answer here, you could think about the possibility of asking whether the format could change.  (To do that, you would want to have an example of the new format ready to show.)

Good luck.

[Cynthia_sas]

Hi:

  There is a way to do what you want using a computed column. Basically, you can group your observations and get your counts summarized, but the normal behavior of PROC REPORT  is to suppress the repetitious display of GROUP or ORDER items. I didn't bother with any summarizing in the program below, since the main point was to show how to use the COMPUTE block to do what you want.

  Basically, at the start of every GROUP item, you need to "grab" the value for the item of interest -- such as COUNTRY or PRODTYPE. Then you have to assign the held value to a computed item. So, you can have your GROUP items still used on the report for grouping, but by adding NOPRINT to the DEFINE statement (after you confirm that the COMPUTED items are being written correctly), you will only see the COMPUTED version of the GROUP items.

  I think the program below will get you started.

cynthia

ods html file='c:\temp\fill_cols.html';

proc report data=sashelp.prdsale nowd;

  title 'See How This Works, then uncomment the NOPRINT option';

  column country dispc prodtype disppt product n;

  define country /group /* noprint */;

  define dispc /computed 'Country' ;

  define prodtype /group /* noprint */;

  define disppt /computed  'Product Type' ;

  define product /group;

  define n /'Count';

  compute  before country;

    holdc = country;

  endcomp;

  compute before prodtype;

    holdpt = prodtype;

  endcomp;

  compute dispc /character length=10;

    dispc = holdc;

  endcomp;

  compute disppt /character length=10;

    disppt = holdpt;

  endcomp;

run;

ods html close;