BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
snip
Obsidian | Level 7

Good morning,
I have a proc report, with a display of tabs according to my "group by" in ods excel.
I take the example of the class table.
If I have 2 sheet according to my "group by" "Sex" (F and M) I want to display all of my columns if I am in the (M) sheet.
and if I am in sheet (F) I want to hide a few columns (example hide weight).
I built this code but it doesn't work!!!
could you help me ?

I specify that I used the class table only to present my problem.

my real data contains around fifty columns  and several sheet

 

snip_1-1698316762668.png

 

snip_2-1698316794441.png

 

proc sort data=sashelp.class out=class ;by sex; run;
data report_data;
set class;
by sex;
if sex ='F' then column_display='limited';
if sex='M' then  column_display='all';
run;
ods excel file='C:\temp\class.xlsx'
options(
embedded_footnotes='yes' sheet_name = "#byval1"  contents="yes");
PROC REPORT data=report_data; 
by sex;
column   age sex name height weight;
define age/group;
define sex /display;
define name /display;
define height/mean;
define weight/sum;

compute weight;
if column_display='limited' then  call define(_col_,'style',"style={visibility=hidden}");
endcomp;
run;
ods excel close;
1 ACCEPTED SOLUTION
7 REPLIES 7
Ksharp
Super User

Try this one :

proc sort data=sashelp.class out=class ;by sex; run;
data report_data;
set class;
by sex;
if sex ='F' then do;column_display='limited';weight=.;end;  /*<-----*/
if sex='M' then  column_display='all';
run;
ods excel file='C:\temp\class.xlsx'
options(
embedded_footnotes='yes' sheet_name = "#byval1"  contents="yes");
PROC REPORT data=report_data; 
by sex;
column   age sex name height weight;
define age/group;
define sex /display;
define name /display;
define height/mean;
define weight/sum nozero ;  /*<-----*/

compute weight;
if column_display='limited' then  call define(_col_,'style',"style={visibility=hidden}");
endcomp;
run;
ods excel close;
Cynthia_sas
SAS Super FREQ
A side note-- in order to test the COLUMN_DISPLAY variable in the COMPUTE block for WEIGHT, the best practice is for the COLUMN_DISPLAY variable to be listed on the COLUMN statement in PROC REPORT. Otherwise, you'll get a message like this in the LOG:
NOTE: Variable column_display is uninitialized.
When worked at a place where I had to turn in my LOG with my REPORTS, notes like this were not allowed because the LOGS were shown to our clients and they would not sign off on a report with a note like this.
Cynthia
Ksharp
Super User
Cynthia,
That PROC REPORT was not writed by me,
I just COPY it form OP and add some sauce to make it work.
snip
Obsidian | Level 7

@Ksharp Thank you Ksharp for your reply.
@Cynthia_sas Thank you Cyntia for the clarification.
I forgot to mention that among my data there are also calculated columns.
This solution works with display and analysis columns but I have difficulty making it work on a calculated column.
Is there a trick to make this work?

snip
Obsidian | Level 7

Hello,

is there anyone who can help me? 

I'm really stuck and I haven't been able to find the solution for almost 2 days 😥😥

 

Ksharp
Super User
Since your requirement is too much, I suggested you to make a MACRO to output a sheet one by one .Like:

ods excel options(sheet_name = "F" );
PROC REPORT data=report_data nowd;
where sex='F';
.......................

ods excel options(sheet_name = "M" );
PROC REPORT data=report_data nowd;
where sex='M';
.......................

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 2111 views
  • 1 like
  • 3 in conversation