suppress Blank rows in proc report /output to xml via odstagsets

[Deepti44]

Hi ,

I'm trying to output data using proc report to xml via odstagsets.excelxp, when i'm grouping variable , veery group variable has a multiple values in other columns.when i'm trying to filter by one of the data variable only 1 associated data field to other column is displaying but not ALL.

Can i know how to suppress blank rows in column1 (c1).Please see the attached Picture.

Image 1 shows that column c1 and c2 and c3  are group variables and has different names associated with it.

when i filter C1 i'm getting only 1 name  associated with c1 but not all NAmes.

Can you please help me  how to supress the blank values in c1 ,c2 and c3 while filtering .

Appreciate your help on this.

[Cynthia_sas]

Hi:
Since we can't see the code you're using, it's hard to make a constructive suggestion. Also, even if you provided your code, without data, somebody would have to make fake data by guessing what your data looked like. It looks like you might have SPANROWS turned on, but that's only a guess.

By default, PROC REPORT suppressess the repetitious display of group and order variables. Have you defined C1, C2 and C3 as GROUP or ORDER usage on the DEFINE statement?

It's not entirely clear to me what your question is. Is your question about how to get the column value on each row? Or is your question about filtering in Excel?

Cynthia

[Deepti44]

cynthia,

I have used c1 ,c2 and c3 as Group on define in Proc report.There are different "Names" associated with C1 variable(distinct values).

When i filter the Output (which is in excel or XmL) for each distinct "C1" data value the report needs to show multiple names when a user filters by "C1"  data value, but when i'm trying to Filter "C1" as shown in the Picture attached, i see blank values as well when i 'm trying to  filter "C1" column instead of "All Names" only "1 Name" is displaying.

Can i know how to suppress this blank Values when i  use Group in the Define statement in Proc report.

[VDD]

like @Cynthia_sas said show your code.

if your code is so private that you cannot convert it to something we can analyze then with your expertise convert the logic to something we can see so that we understand your request.

it is easy to give you possibilities but if you play like the part of a manager and only have an idea it is hard for us to engage with your needs of your logic processing in your request.

Please be kind and provide all that is possible unless it jeopardizes you job.  If that is the case then consult a consultant with your company.

[Cynthia_sas]

Hi: Unfortunately without code or data, it's nearly impossible to figure out how to help you. Are you using SPANROWS? Are you using AUTOFILTER as a suboption? Without code to look at, you're expecting people to guess what your code looks like. If you're seeing something like this:

in your autofilter when you create a report with REPORT, like this you will see the word "blanks" in the list for the autofilter. However,  there is a way to remove the word "blanks" from the autofilter list in Excel, but it involves using a COMPUTE block to get this:

My example used SASHELP.PRDSALE and with either approach, I see all the group values in the drop down list. So if you are not seeing all the group values, plus the word "blanks" in the autofilter list, then there's something about either your code or your data that needs to be fixed. Since we can't see either of those things, it's hard to make any other comment.

  
Cynthia

[Deepti44]

cynthia,

Here is my code.. as i said its  for every C1( value) there are mutliple names associated with it like 1 to many relationship.Hope the below code helps .

ods _all_ close;
ods tagsets.excelxp file="MARKETVALUE_&run_date..xml"

style=sasweb
options(Sheet_name='Data'
Orientation= "LANDSCAPE"
autofit_height='yes'
Embedded_Titles='OFF'
frozen_headers ='1'
Autofilter='All'
whitespace='NORMAL');
options missing=' ';

PROC REPORT DATA=test spanrows nowd MISSING
style(column)=[BACKGROUNDCOLOR=white BORDERCOLOR=black
BORDERWIDTH=1
FONT=(Verdana, 8pt, NORMAL) ]
style(header)=[BACKGROUNDCOLOR=aliceblue BORDERCOLOR=black
BORDERWIDTH=1
FONT=(Verdana, 8pt, bold)];
TITLE " REPORT";
COLUMN C1 C2 C3 opportunity_name

;

DEFINE C1 /order center nozero width=3 style(column)={vjust=m textalign=c} ;
DEFINE C2 /group width=4 style(column)={vjust=t just=c} ;
DEFINE c3 /GROUP 'Account ID' center width=8 style(column)={vjust=t just=c};

DEFINE name /group 'opportunity Name' center width=8 style(column)={vjust=t just=c};

ods _all_ close;

[Cynthia_sas]

Hi:
It helps a bit, but I really do not want to make fake data, especially since I don't understand why C1 is order and c2, c3 and name are GROUP items. That doesn't make sense to me. Also, in your code, you show Opportunity_name in the COLUMN statement, but NAME on the define statement. Are you sure that your code runs?

You did not comment on whether your problem was shown in my first screen shot (with the word "blanks" in the filter). Nor did you comment on whether the second screen shot, without the word "blanks" was what you were aiming for.

Without data, no one can run your code. If you cannot provide your data to test with, then use a SASHELP data set (such as SASHELP.PRDSALE or SASHELP.SHOES) to illustrate your issue.

Cynthia

[Deepti44]

cynthia,

The code i provided is running(but apologize "C1" is  defined as a Group) in my code as i was testing to remove blanks under a group variable  i changed it to "order".

And  as I cannot share the actual data, I illustrated with C1, C2, C3,NAME columns.

Please see the attached document for a clearer example as somehow i cannot able to paste the snippets in my email.

Hope this helps.

[Cynthia_sas]

Hi:

  Is that your REAL data in the screen shot or fake data that you put into Excel? You could generate fake data that to use with your program. Post it in the form of a DATA step program.

  Honestly, I do not understand what your question is. When I turn autofilter on, I can see ALL the values in my first column, including the word "blanks" -- I have explained that if you want the GROUP item to only appear 1 time (as you show when c1 is shown as 290), then ALL the rest of the rows for the group will be blank. You have two choices for how the empty rows should be presented: 1) show an empty cell on each row or 2) use SPANROWS to have 1 big spanned row. Those are your choices.You seem to be saying that when you use the filter, you don't see all your choices. That is NOT my experience. I used SASHELP.PRDSALE for this report. It has multiple columns with SPANROWS making spanned headers and has autofilter turned on:

  In my test, using SASHELP.PRDSALE, I see what I expect. In the column for COUNTRY, I see CANADA and GERMANY and (Blanks); in the column for REGION, I see EAST, WEST and (Blanks); in the column for PRODTYPE, I see FURNITURE, OFFICE and (Blanks) -- I know the (Blanks) isn't what I want autofilter to show me. But Excel is only showing what it rendered. There ARE blanks in the empty rows under CANADA and under GERMANY; and there ARE blanks in the empty rows under EAST and WEST. So autofilter is behaving as it should.

  Please clarify your question. In your latest post, you say you want to "remove blanks under a group variable" . I get that you can't or don't want to post data -- although what you put in your Word doc did not look all that confidential and unshareable to me. Look at my output above, the "group" for my column 1, Canada, spans from Row 2-Row 11 and the group for Germany spans from row 12-Row 21. How can you remove blanks under Canada? That doesn't make sense to me. Sending your code again and a picture of a clearly typed partial Excel sheet does not help me understand.

  So using SASHELP.PRDSALE and the picture I have just posted above, how would you want the output to look different on rows 2-11 for the CANADA rows and how you would want the output to look different on rows 12-21 for the Germany rows??

  There is a possible workaround, the only one that I can think of,as I showed in my previous example, where you can tell PROC REPORT to fill in the blank cells on each row using a COMPUTE block.

Cynthia