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

Hello, 

 

I used "proc report" to output my final summary to excel file. 

However, the yellow highlighted space below is gone from the output excel. 

I am wondering how I can keep it. 
Thank you. 

 

Ivy_1-1696270189050.png

 

Also,  I am wondering why the "." change to "0" in the excel output. 

 

Ivy_2-1696270430440.png

 

Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

Hi:

  I find that I need to use INDENT= with PROC REPORT in order to have any indenting respected. You did not show your code so my guess is that you either padded your line on the left with leading blanks (which doesn't work) or else you used the LEFTMARGIN= style override (which works for PDF and RTF). When I use INDENT= along with some helper variables to specify which lines need to be indented, Excel uses my INDENT= style override, as shown below:

Cynthia_sas_0-1696279616455.png

  Hope this helps,

Cynthia

View solution in original post

6 REPLIES 6
ballardw
Super User

We do not have your starting data or your code to create the report or to send the result to Excel so really cannot answer.

 

In SAS most places where the N statistic is used the result is a 0 when counting something if there are none.

 

And example that you should be able to run as the SASHELP.CLASS set should be available.

proc freq data=sashelp.class;
   tables sex*age /norow nocol nopercent;
run;

There are no records with a  Sex=F and Age=16 so the count result is 0.

Ivy
Quartz | Level 8 Ivy
Quartz | Level 8

Thank you very much.

 

The following are the codes with "." in the name column which output as 0 in the excel output. 

 

proc report data=final split='~';
column ord name trt1 trt2 flag pvalue;
define ord / order noprint;
compute name;
if flag=0 then do;
call define(_col_,'style','style=[fontweight=bold]');
end;
else do;
call define(_col_, 'style','style=[asis=on]');
end;
endcomp;

run;

Cynthia_sas
SAS Super FREQ

Hi:

  I find that I need to use INDENT= with PROC REPORT in order to have any indenting respected. You did not show your code so my guess is that you either padded your line on the left with leading blanks (which doesn't work) or else you used the LEFTMARGIN= style override (which works for PDF and RTF). When I use INDENT= along with some helper variables to specify which lines need to be indented, Excel uses my INDENT= style override, as shown below:

Cynthia_sas_0-1696279616455.png

  Hope this helps,

Cynthia

Ivy
Quartz | Level 8 Ivy
Quartz | Level 8

Thank you, Cythnia. 

When I used your codes, it said "Variable indent is uninitialized".

It was in the test data. I am wondering how to fix it.

 

28 proc report data=test split='~';
29 column ord name trt1 trt2 indent pvalue;
30 define ord / order noprint;
31 compute name;
32 if indent=0 then do;
33 call define(_col_,'style','style=[fontweight=bold]');
34 end;
35 else if indent=1 then do;
36 call define(_col_, 'style','style=[asis=on]');
37 end;
38 endcomp;
39
40 run;

NOTE: Variable indent is uninitialized.
NOTE: There were 26 observations read from the data set WORK.TEST.
Ivy_1-1696333847929.png

 

 

 

 

Cynthia_sas
SAS Super FREQ

Hi:

  INDENT= is a style override and in my program, I have a helper variable called INDENT that I use to determine which INDENT= value to use. In the DATA step program at the top of my screen shot. So I see that you seem to have created a helper variable called INDENT. But you have it at the end of the report row in your COLUMN statement.

  Notice in my program, how my "helper" variables are all placed to the left side of the TEXT variable. This is because of the left-to-right rule of how PROC REPORT processes the items on the report row. I see you have an INDENT variable listed on your COLUMN statement, but if you want to test INDENT in order to change the style for the NAME variable, then you have to use the same left-to-right ordering that I used. Your INDENT variable will need to be placed on the COLUMN statement before the NAME variable. Since you did not provide any data to work with, it's impossible to run your program without making.

  Also, your code does not use the INDENT= style option. I only see  ASIS=ON. I am not sure that will be respected by Excel when it opens the file.

Cynthia

Ivy
Quartz | Level 8 Ivy
Quartz | Level 8
Thank you very much, Cynthia!
It is really helpful.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 628 views
  • 0 likes
  • 3 in conversation