The SAS Output Delivery System and reporting techniques

Formatting using proc report

Reply
N/A
Posts: 0

Formatting using proc report

Hi friends,

I want to display a particular record with some space . For example:

January $1,499,934 + 9.3
February $1,344,610 + 7.0
MARCH $1,542,826 + 7.5
1ST QUARTER $4,387,370 + 7.9

The quarter vales for all the months should be displayed with one space(i.e. under the 2nd letter of each month). I am displaying the text (1ST QUARTER) based on a condition which is checked using the rank value of the data's. My code looks like

COMPUTE AFTER rank_i;
CALL DEFINE(_ROW_,'STYLE', 'STYLE={FONT_WEIGHT=BOLD}');
IF rank_i = 3 THEN
mon= " 1ST QUARTER ";
ELSE IF rank_i = 2 THEN
mon= " 2ND QUARTER ";
ELSE IF rank_i = 1 THEN
mon= " 3RD QUARTER ";
ELSE IF rank_i = 0 THEN
mon= " 4TH QUARTER ";
LINE " ";
ENDCOMP;

The quarter values are displayed with one space in the SASList but when i convert it into PDF file using ODS PDF the space is not getting displayed. Can anyone please help me on this ASAP??
SAS Super FREQ
Posts: 8,742

Re: Formatting using proc report

Hi:
I am not currently on a computer with SAS or my sample library; however, you can do a search on support.sas.com FAQ for further information.

One thing to try is using the ASIS=ON style attribute, which preserves leading spaces:

define month / sum style(column)={asis=on};

The other thing to try is to pad the beginning of the string with a non-breaking space -- so, if ODS ESCAPECHAR is set to '~', then the non-breaking space would be ~_ (tilde underscore).

mon= "~_2ND QUARTER ";

You would need an ODS ESCAPECHAR statement in order for this to work:
ODS ESCAPECHAR='~';

However, with a proportional-spaced font, there is NO guarantee, that the single non-breaking space will be EXACTLY under the 2nd character of the month name above. In a mono spaced font, the letter 'i' takes the same amount of space as the letter 'w' (which is why it looks OK in LISTING), but in a proportional spaced font, the letter 'J' for June or July, could take up less space on the line than the letter 'A' for August.

A third option is to use the INDENT= style attribute on a CALL DEFINE statement for the row that contains the quarter information.

For the best approach in this situation, you best bet might be to contact Tech Support. Good luck.
cynthia
N/A
Posts: 0

Re: Formatting using proc report

Hi,

Thanks.. I tried using the ASIS=On option and it worked fine. Can you please help me with another doubt? Can we use formatting options like BOLD in the title statement while using PROC REPORT?
For example:
I want the title to be displayed in the left and right corners on the top of the page in BOLD. How can i use the title statement to display in this manner? Or is there any other options for doing this. Please help ASAP.

Thanks,
Sandhya
N/A
Posts: 0

Re: Formatting using proc report

Hi,

Thanks.. I tried using the ASIS=On option and it worked fine. Can you please help me with another doubt? Can we use formatting options like BOLD in the title statement while using PROC REPORT?
For example:
I want the title to be displayed in the left and right corners on the top of the page in BOLD. How can i use the title statement to display in this manner? Or is there any other options for doing this. Please help ASAP.

Thanks,
Sandhya
SAS Super FREQ
Posts: 8,742

Re: Formatting using proc report

Hi:
The TITLE (and FOOTNOTE) statement accepts standard SAS/Graph options for justification and bolding, such as:
[pre]
title bold j=l 'Left Justified'
j=c 'Center'
j=r 'Right Justified';

OR

title2 bold j=l 'Left Justified'
c=red j=c 'Center'
c=blue j=r 'Right Justified';
[/pre]
BUT, these title statements may work differently in different destinations. For example, if you use LISTING destination, these text strings will not have differing justification, if you use the MSOFFICE2K tagset, each of these text strings would be on a different line, but in the PDF destination, then the text strings are justified appropriately.

However, SAS titles and footnotes already have BOLD turned on in the style template, so I am curious what destination you are using where the titles are NOT bolded.

Since SAS Title statements are global statements, they are not affected by Proc Report, Proc Tabulate, etc. The SAS title as shown above would work in a PROC MEANS step as well as a PROC REPORT step. It is the destination in ODS that determines how the title will be rendered. And, of course, there is no style in the LISTING destination.

You can use in-line formatting in the SAS TITLE statement, but should not need to to accomplish BOLDing or differing justification. For more help with SAS Titles and using SAS/Graph options in the title (such as Bold, Italic, Color, Font, Height), consult the SAS/Graph documentation. If you need more help, the Tech Support FAQ has some information about using these options in the SAS TITLE or FOOTNOTE.

Good luck,
cynthia
N/A
Posts: 0

Re: Formatting using proc report

Hi,

Thanks for your reply. I got the title displayed in the left and right corners at the top of the report. But now the whole report is getting aligned from the left and right corners. I want the report to remain aligned in center. I specified leftmargin option for the report but stil the data's arre getting displayed from the left corner.
Can you please help me with this ASAP?

Thanks,
Sandhya
SAS Super FREQ
Posts: 8,742

Re: Formatting using proc report

Hi:
It is very perplexing. I do not understand how the TITLE statement could possibly be affecting the data table on the report. Perhaps you have

OPTIONS NOCENTER someplace in your code???

Your best bet for a quick resolution to this is to contact Tech Support, there must be something else happening in your code that is affecting the data table. PDF does support:

options topmargin=1in bottommargin=1in leftmargin=1in rightmargin=1in;

It may be possible that the report is centered -starting- at the left margin you gave -- Tech Support can determine whether some combination of options are conflicting with each other.

cynthia
N/A
Posts: 0

Re: Formatting using proc report

Hi,

The BOLD option specified in the TITLE for the PROC REPORT is not working. Is there any other option for providing the formatting options for the TITLE.Please reply ASAP.

Thanks,
Sandhya
SAS Super FREQ
Posts: 8,742

Re: Formatting using proc report

Hi:
You truly have a problem to report to Tech Support. BOLD on a TITLE statement has NOTHING to do with PROC REPORT. The TITLE statement lives outside of any given procedure and is a global statement.

The only place where BOLD would NOT work would be the LISTING or OUTPUT window. There are a few guesses I would make IF your destination is PDF, RTF or HTML and the BOLD option is not working -- somehow your style template (which has BOLD turned on for the SAS System TITLE) is not being applied OR there's something wrong with your install of SAS OR there's something wrong with your template store.

At this point, you have a problem that really needs to have Tech Support open a track in order to follow the problem and resolve the issue. Good luck!
cynthia
N/A
Posts: 0

Re: Formatting using proc report

Hi ,

Could you please help me with this? I want to create PDF files dynamically in SAS.
For example: I have a unique code value in my input file.I have to read the code values And for each unique code value i want a PDF file to be created with the filename having the code value. I have used proc report for creating the PDF's layout and macros calls for generating the report each time.

Can you please help me with this ASAP?

Thanks,
San
SAS Super FREQ
Posts: 8,742

Re: Formatting using proc report

Hi:
Generally speaking, you could have a couple of different designs to do what you want. This one just shows the creation of the PDF file within a macro program:
[pre]
** define macro program ;
%macro dorept(code=);
ods pdf file="&code..pdf";
*** ... proc report code for 1 report and probably where stmt to subset data;
ods pdf close;
%mend;

** invoke macro program to produce PDF output;
** and use macro var CODE for the PDF file name;
%dorept(code=new);
%dorept(code=stuff);
%dorept(code=every);
%dorept(code=time);
[/pre]

It sounds like you have the bulk of the work done if you already have the macro program to read the data and produce the report.

cynthia
N/A
Posts: 0

Re: Formatting using proc report

Hi,

My code is something lik this.

** define macro program ;
%macro dorept(code=);
ods pdf file="&code..pdf";

IF SUBSTR(report1,1,5) = "CDHCP" THEN DO;
CALL EXECUTE ('%reports('||report1||',t,1)');


ods pdf close;
%mend;


** invoke macro program to produce PDF output;
** and use macro var CODE for the PDF file name;
%dorept(code=new);
%dorept(code=stuff);
%dorept(code=every);
%dorept(code=time);


The statements bolded in between are the macro calls which in turn are used to generate the reports using PROC REPORT by checking some conditions.

Can i use macro calls in between the ODS option for creating the PDF files dynamically? But there seems to be some problem while using this code. the file is getting created but as empty file and not as pdf.. Moreover the unique code value (for eg: code1)is to be read from a input file and passed to the macro call as

%dorept(code=&code1);

Is this correct? Or can you suggest me what is the problem here and how should i proceed with this further?Please reply ASAP.

Thanks,
San
SAS Super FREQ
Posts: 8,742

Re: Formatting using proc report

Usually, your CALL EXECUTE is within a DATA step program like this:
[pre]
data whatever;
set something;
call execute(argument);
run;
[/pre]

Where you want to EXECUTE some set of statements for every observation in the input data set or for some selected subset (like the first or where a condition is met). According to the documentation:

If argument resolves to a macro invocation, the macro executes immediately and DATA step execution pauses while the macro executes. If argument resolves to a SAS statement or if execution of the macro generates SAS statements, the statement(s) execute after the end of the DATA step that contains the CALL EXECUTE routine. CALL EXECUTE is fully documented in SAS Macro Language: Reference.


So it is possible that you have some timing issues if your CALL EXECUTE it in a DATA step program which is IN a macro program and IS CALLING another macro program which generate PROC REPORT statements.

I call your attention to this SUGI paper:
http://www2.sas.com/proceedings/sugi30/027-30.pdf

It outlines many of the pitfalls of CALL EXECUTE. There are other problems that you may run into that are just related to quoting issues in your CALL EXECUTE statement. I generally follow these steps when developing a macro program:

1) have a working SAS program with no macro variable references that produces the output I want for one condition or group

2) Modify the working SAS program to include variable references such as
%let group = Asia;
where Region = "&group";
to make sure that my macro variable substitution will result in the same report as my hardcoded example.

3) Make a macro program that can be invoked repetitively for each one of a group:
%doreg(group=Asia)
%doreg(group=Pacific)
%doreg(group=Canada)
...and make sure that the macro program generates the correct output for each selection

4) At this point I have 2 choices:
a) write a program to dynamically generate the macro invocations as shown in 3 and then %include the file with the generated code
OR
b) write a program to use CALL EXECUTE to invoke the macro directly from within the DATA step program.

I would probably do 4a first before I moved onto 4b because resolving issues in 4a would help me decide whether I was going to have timing issues in 4b, depending on what kinds of SAS statements my macro program was generating. And, I frequently find that my macro programs are not so complex that I need to move to 4b after I have successfully written program 4a. For example:
[pre]
** define the macro program from working SAS program;
** group is for my where statement;
** fname is for the filename in the ODS statement;
** way is for the filename -- either;
** include method (way=INCL) or ;
** call execute method (way=CEXEC);
** Also, use a bit of conditional logic in the;
** PROC REPORT code;
** I need a variable for the WHERE clause that will match what is in the data (&group);
** BUT I do NOT want to have a / in the PDF file name because my operating;
** system does not allow special characters in filenames. so my filename;
** will come from &FNAME.&WAY macro variables concatenated together;

%macro doreg(group= , fname=, way=);
ods pdf file="c:\temp\&fname.&way..pdf";
title "&Group Report: File is &fname.&way..pdf";
proc report data=sashelp.shoes nowd;
where region = "&group";
column region product sales;
define region / group;
define product/group;
%if &group = Asia %then %do;
define sales /sum 'Asia Sales';
%end;
%else %do;
define sales/ sum 'Other Sales';
%end;
rbreak after /summarize;
run;
ods pdf close;
%mend;

** 3) Prove that my macro program can be invoked repetitively;
** and that it produces the correct output;
%doreg(group=Asia, fname=Asia, way=REP);
%doreg(group=Canada, fname=Canada, way=REP);


** 4a) make an ASCII text file with the invocation statements;
data makelist;
set sashelp.shoes;
by Region;
if first.region then do;
fname = translate(Region,' ','/');
fname = left(compress(fname));
file 'c:\temp\invoke_reg.txt';
put @1 '%doreg(group=' Region ', fname=' fname ',way=INCL);';
end;
run;


** 4a) now invoke the macro program for each region using the TXT file;
** look at the TXT file in NOTEPAD to see what was generated;
%include 'c:\temp\invoke_reg.txt';

** 4b) Turn 4a program into a CALL EXECUTE program;
** make an ASCII text file with the invocation statements;
** I prefer to build my invocation statement into a program variable name;

data makelist;
set sashelp.shoes;
by Region;
if first.region then do;
fname = translate(Region,' ','/');
fname = left(compress(fname));
invoke = '%doreg(group='||trim(Region)|| ', fname='||trim(fname)||',way=CEXEC)';
call execute(invoke);
end;
run;
[/pre]

If you are having a problem, I suggest that you go all the way back to step 1 and make sure you have a working SAS program (without call execute). Then go to step 2 and make sure that your program (PROC REPORT) works correctly to create ONE PDF file when you insert simple macro variable references into the program. Then move to step 3 and design a macro program that can be invoked repetitively and produces the correct output for each group. Then move to step 4a.

By that point, I'm betting that you will have discovered all your various issues. Otherwise, my only other idea would be for you to contact Tech Support for further help.
cynthia
N/A
Posts: 0

Re: Formatting using proc report

Hi,

I want to display the LINE statement in PROC REPORT with some specific alignment. My report looks like

OCTOBER $1,639,917 + 10.4
NOVEMBER $1,602,019 + 6.4
DECEMBER $1,579,400 + 4.7
4TH QUARTER $4,821,336 + 7.2

04 MONTH TOTAL $5,834,837 + 7.8

12-MONTH TOTAL $18,341,351 + 7.9

The 04 month total that is bolded is the one i have displayed using the line statement. The other month values and the 12 month totals are defined as columns and computed in the PROC REPORT.

I have some 10 reports to be displayed with the same format. So i use macro calls to call the PROC REPORT each time.
But the number of digits in the 04 month's total varies so i am not able to align it exactly with the other values.

The above report is the one i got with correct alignment.But i get the report as below when the dollar value is less(4 or 5 digits).

OCTOBER $1,17 + 10.4
NOVEMBER $1,019 + 6.4
DECEMBER $1,579 + 4.7
4TH QUARTER $336 + 7.2

04 MONTH TOTAL $5,83 + 7.8

12-MONTH TOTAL $18,341,351 + 7.9

I have used right alignment for all the data's. And i need the 04 month data's also to be aligned with the others.So i tried with using the IF condition statement which checks the length of the data of 04 month and display's the line according to the number of digits.

COMPUTE AFTER k;
LINE ' ';
lx1=length("&yy1doll");
if lx1 <= 6 then
LINE @01 "&cur_mon-MONTH TOTAL" @36 "&yy2doll"
@53 "&yy2pct" @85 "&yy1doll" @101 "&yy1pct";
LINE ' ';
ENDCOMP;

But it seems the condition is not being checked and the LINE statement is displayed even when the number of digits is greater than 6.

Can you please help me with this?? Or is there any other option to display the data with exact alignment as i mentioned??Please reply ASAP.

Thanks,
Sandhya
N/A
Posts: 0

Re: Formatting using proc report

COMPUTE AFTER k;
LINE ' ';
lx1=length("&yy1doll");
if lx1 <= 6 then
LINE @01 "&cur_mon-MONTH TOTAL" @36 "&yy2doll"
@53 "&yy2pct" @85 "&yy1doll" @101 "&yy1pct";
LINE ' ';
ENDCOMP;

But it seems the condition is not being checked and the LINE statement is displayed even when the number of digits is greater than 6.

Can you please help me with this?? Or is there any other option to display the data with exact alignment as i mentioned??Please reply ASAP.

Thanks,
Sandhya
Ask a Question
Discussion stats
  • 17 replies
  • 781 views
  • 0 likes
  • 2 in conversation