topic Re: hide or delete a column according to my group by in proc report ods excel in SAS Programming

hide or delete a column according to my group by in proc report ods excel

snip — Thu, 26 Oct 2023 10:47:50 GMT

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

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;

Re: hide or delete a column according to my group by in proc report ods excel

Ksharp — Thu, 26 Oct 2023 11:47:26 GMT

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;

Re: hide or delete a column according to my group by in proc report ods excel

Cynthia_sas — Thu, 26 Oct 2023 16:22:39 GMT

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

Re: hide or delete a column according to my group by in proc report ods excel

snip — Thu, 26 Oct 2023 17:31:59 GMT

@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?

Re: hide or delete a column according to my group by in proc report ods excel

snip — Fri, 27 Oct 2023 10:19:38 GMT

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 😥😥

Re: hide or delete a column according to my group by in proc report ods excel

Ksharp — Fri, 27 Oct 2023 11:31:36 GMT

Cynthia,
That PROC REPORT was not writed by me,
I just COPY it form OP and add some sauce to make it work.

Re: hide or delete a column according to my group by in proc report ods excel

Ksharp — Fri, 27 Oct 2023 11:33:46 GMT

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';
.......................

Re: hide or delete a column according to my group by in proc report ods excel

snip — Fri, 27 Oct 2023 12:26:08 GMT

Thank you