PROC Report with The column that may disappear

[DavidBesaev]

Hi! Need your help! I have a table, where some columns can be or disappear after TRANSSPLITCOLUMNS, it,s automatic report and if some column are dissapear, then the report is not working correctly.

hear example of code:

PROC Report DATA=WORK.SORTTempTableSorted
style(column)=[font_style=Roman font_width=normal TEXTALIGN=CENTER FONTSIZE=7pt]
style(head)=[foreground=black font_weight=bold font_style=Roman FONTSIZE=7pt TEXTALIGN=CENTER width=100pt background=#afdafc VERTICALALIGN=center] missing;
BY CHANNEL;
options label;
label channel="Канал" COUNT_OF_COM="Количество коммуникаций" EMAIL="Игроков с коммуникациями по Email" SMS="Игроков с коммуникациями по SMS" WWW="Игроков с коммуникациями по WWW" MORE_THEN_1 = "Из них получили коммуникацию более чем в одном канале";
format COUNT_OF_COM EMAIL SMS WWW MORE_THEN_1 nlnum10.;
column channel COUNT_OF_COM EMAIL SMS WWW MORE_THEN_1 ;
DEFINE CHANNEL / order noprint;
DEFINE COUNT_OF_COM / display style(header)=[width=75pt] ;
DEFINE EMAIL / display style(header)=[width=75pt] ;
DEFINE SMS / display style(header)=[width=75pt] ;
DEFINE WWW / display style(header)=[width=75pt] ;
DEFINE MORE_THEN_1 / display style(header)=[width=75pt] ;
compute before _page_ / center style=[font_weight=bold foreground=black background= #afdafc FONTSIZE=8pt];
pgstr = catx(' ',channel);
line pgstr $varying100.;
endcomp;
run;

Can I add something in the code below with no column printed the report without the column, as in proc print?

[DavidBesaev]

I change "display" to "EXCLUSIVE" and it works, BUT label doesn't work. What i can to do?

[RW9]

Firstly we need to know what is "not working"?  What happens and what do you expect to happen?  If its just the data doesn't contain the variables you expect, then first there is a problem with previous code - fix it there.  You cuold patch it by creating an empty dataset with all the columns and setting these two together.  Also, code formatting is very important for readability, you can use the {i} code windows (just above post area) to preserve formatting:

proc report data=work.sorttemptablesorted
      style(column)=[font_style=roman font_width=normal textalign=center fontsize=7pt]
      style(head)=[foreground=black font_weight=bold font_style=roman fontsize=7pt textalign=center width=100pt background=#afdafc verticalalign=center] missing;
  by channel;
  format count_of_com email sms www more_then_1 nlnum10.;
  column channel count_of_com email sms www more_then_1;
  define channel      / "Канал" order noprint;
  define count_of_com / "Количество коммуникаций" display style(header)=[width=75pt];
  define email        / "Игроков с коммуникациями по Email" display style(header)=[width=75pt];
  define sms          / "Игроков с коммуникациями по SMS" display style(header)=[width=75pt];
  define www          / "Игроков с коммуникациями по WWW" display style(header)=[width=75pt];
  define more_then_1  / "Из них получили коммуникацию более чем в одном канале" display style(header)=[width=75pt];

  compute before _page_ / center style=[font_weight=bold foreground=black background= #afdafc fontsize=8pt];
    pgstr = catx(' ',channel);
    line pgstr $varying100.;
  endcomp;
run;

Note I moved the labels for clarity.

[DavidBesaev]

Thanks for the reply and help!

About the problem: data can either contain or not contain a specific column (for example 'www').

If data contain this coumn - everything is ok, if not - the entire table disappears from the report completely.

Then I change in "Define" point  Display -> EXCLUSIVE and it works, but there is no lable, only name of the table.

proc report data=work.sorttemptablesorted
      style(column)=[font_style=roman font_width=normal textalign=center fontsize=7pt]
      style(head)=[foreground=black font_weight=bold font_style=roman fontsize=7pt textalign=center width=100pt background=#afdafc verticalalign=center] missing;
  by channel;
  format count_of_com email sms www more_then_1 nlnum10.;
  column channel count_of_com email sms www more_then_1;
  define channel      / "Канал" order noprint;
  define count_of_com / "Количество коммуникаций" display style(header)=[width=75pt];
  define email        / "Игроков с коммуникациями по Email" display style(header)=[width=75pt];
  define sms          / "Игроков с коммуникациями по SMS" display style(header)=[width=75pt];
  define www          / "Игроков с коммуникациями по WWW" EXCLUSIVE style(header)=[width=75pt];
  define more_then_1  / "Из них получили коммуникацию более чем в одном канале" display style(header)=[width=75pt];

  compute before _page_ / center style=[font_weight=bold foreground=black background= #afdafc fontsize=8pt];
    pgstr = catx(' ',channel);
    line pgstr $varying100.;
  endcomp;
run;

[RW9 · Accepted solution]

The first I would check why the steps before this are creating data which doesn't conform to what you expect for the report - that could be an issue in itself.  As I said you could fudge it - its not ideal but:

proc sql;
  create table tmp (channel char(200),count_of_com char(200),email char(200),sms char(200),www char(200),more_than_1 char(200));
quit;

data yourdata;
  set tmp yourdata;
run;

This will create an empty dataset with all the required varaibles, and padd out the data with all variables.

[DavidBesaev]

Thank you very much! Now it work perfectly