BookmarkSubscribeRSS Feed
deleted_user
Not applicable
Hi Friends,

I want to display the first record once again after all the observations are displayed. Please help me. Is it possible to do that.

Thanks,
selvi.
15 REPLIES 15
Olivier
Pyrite | Level 9
Why don't you repeat the record in the data itself ? Something like :

DATA repeated ;
SET yourData
yourData (OBS=1) ;
RUN ;

PROC REPORT DATA = repeated ... ;
...
RUN ;
Cynthia_sas
SAS Super FREQ
But, depending on whether he was using ORDER or GROUP variables in the report, just outputting an extra obs might not guarantee that the duplicate obs came AFTER the other observations, as he described.

I'm curious about what it is that Selvi is trying to accomplish. For example..consider this detail data of 6 rows: [pre]
Region Product Sales
Asia Slippers $13,000
Asia Boots $25,000
Asia Casual $40,000
Pacific Slippers $15,000
Pacific Boots $20,000
Pacific Casual $30,000
[/pre]

It sound to me like he's asking for this:

[pre]
Region Product Sales
Asia Slippers $13,000
Asia Boots $25,000
Asia Casual $40,000
Pacific Slippers $15,000
Pacific Boots $20,000
Pacific Casual $30,000
Asia Slippers $13,000

[/pre]
But repeating row 1 doesn't make sense for this 6 rows of data. So there must be something about his data, where the first observation is significant for some other reason than just being a row of data.

To me, it almost sounds as if the data already contains totals or something else of significance and he wants to repeat this special obs at the top of a set of rows and at the bottom of a set of rows. Perhaps like this:
[pre]
Region Product Sales
Asia $78,000
Asia Slippers $13,000
Asia Boots $25,000
Asia Casual $40,000
Asia $78,000

Pacific $65,000
Pacific Slippers $15,000
Pacific Boots $20,000
Pacific Casual $30,000
Pacific $65,000
[/pre]

Since PROC REPORT can do a summarize before AND a summarize AFTER, it would be possible to generate something like the above example with this code:
[pre]
ods html file='c:\temp\shoebreak.html' style=sasweb;
proc report data=sashelp.shoes nowd;
column region product sales;
define region /group;
define product /group;
define sales /sum;
break before region /summarize;
break after region /summarize;
compute after region;
line ' ';
endcomp;
run;
ods html close;
[/pre]
Without a more concrete example of the desired output AND some idea of what the input data actually looks like, it is hard to say whether PROC REPORT will accomplish his goal or not.

cynthia
deleted_user
Not applicable
Hi Cynthia,
I need the output as same as below.

Region Product Sales
Asia Slippers $13,000
Asia Boots $25,000
Asia Casual $40,000
Pacific Slippers $15,000
Pacific Boots $20,000
Pacific Casual $30,000
Asia Slippers $13,000

Code:-
proc report data=cdhcpgrep nowd
style(report)={rules=none frame=void just=center
cellspacing=3 cellpadding=3}
style(header)={font_face="Times New Roman"
font_size=14pt
background=white}
style(column)={font_face="Times New Roman" font_size=12pt};
title;
column( ' TOTAL PHARMACEUTICAL DRUG STORE AND HOSPITAL PURCHASES ' mon y2dolt y2pctt y1dolt y1pctt rank_i);
define mon /display " " format=$month.;
define y2dolt/ analysis sum format=dollar12. " &pre_y4"
style(column)={just=center};
define y2pctt/ analysis sum "% CHG/&pre_y2-&pre1_y2 "
format=negparen5.2 style(column)={just=center};
define y1dolt/ analysis sum format=dollar12. " &cur_y4"
style(column)={just=center};
define y1pctt/ analysis sum "% CHG/&cur_y2-&pre_y2 "
format=negparen5.2 style(column)={just=center};
define rank_i / descending group noprint;
break after rank_i / ol summarize skip;
rbreak after / summarize skip;
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;
compute after;
line " ";
mon="12-MONTH TOTAL";
call define(_ROW_,'style', 'style={font_weight=bold}');
line @37 " &cur_mon-MONTH TOTAL &yy2doll &yy2pct"
" &yy1doll &yy1pct ";
endcomp;
run;


And also i have some doubts in PROC Report.

1) Do we apply stylelike bold,,font to line " " within compute block.
2) break after rank_i / ol summarize skip; - This statement is working good in saslist. but in my PDF i didn't get the same output as in SASLIST.
3) In the above code, the line @37 is coming after the final summarization. But i need it before the final summarization. ( before 12 MONTH TOTAL).

Thanks,
Selvi.
Cynthia_sas
SAS Super FREQ
Selvi... your questions:

1) Do we apply stylelike bold,,font to line " " within compute block.
-- yes you can either do this:[pre]
proc report data=whatever nowd
style(lines)={font_weight=bold}
style(summary)={font_weight=bold}
style(header)={background=yellow};[/pre]
on the PROC REPORT statement or you can do this (change the compute block lines):
[pre]
compute after /
style={font_weight=bold};
*** more code and LINE stmts;
endcomp;[/pre] OR if you wanted the BREAK/RBREAK styles to be different, you could do this:[pre]
break after brkvar / summarize
style={font_weight=bold};
rbreak after / summarize
style={font_weight=bold};[/pre]

2) break after rank_i / ol summarize skip; - This statement is working good in saslist. but in my PDF i didn't get the same output as in SASLIST.
--the SUMMARIZE will work in both LISTING and PDF. The SKIP will NOT work in PDF, RTF or HTML. This is explained in this Tech Support note: http://support.sas.com/techsup/unotes/SN/002/002549.html and several workarounds are listed here (including one for skip): http://support.sas.com/rnd/base/topics/templateFAQ/repoption.html

3) In the above code, the line @37 is coming after the final summarization. But i need it before the final summarization. ( before 12 MONTH TOTAL).
-- In the PROC REPORT documentation, there is a section called "How PROC REPORT Builds a Report". In that section, it explains how PROC REPORT works and that "PROC REPORT first constructs the break lines that are created with the BREAK or RBREAK statement or with options in the BREAK window. If there is a compute block attached to the break, then PROC REPORT then executes the statements in the compute block." This means that your summary line from the RBREAK AFTER will come before any LINE statments in the COMPUTE AFTER.

The other issue that you're going to have with the technique shown in your compute block is that the @37 will work in the LISTING destination, but it will be difficult to make things line up in PDF the way you want.

I see 2 overall problems with this approach: [pre]
Region Product Sales
Asia Slippers $13,000
Asia Boots $25,000
Asia Casual $40,000
Pacific Slippers $15,000
Pacific Boots $20,000
Pacific Casual $30,000
Asia Slippers $13,000
[/pre]
1) unless you made your SALES equivalent variables display variables, it would be impossible to prevent the second "$13,000" from getting added into the summary line.
2) if you are somehow trying to bypass PROC REPORT's summary process by inserting a line above the PROC REPORT summary line -- I don't actually think you can do this with PROC REPORT.

I wonder if you have already created summary information with PROC MEANS or the DATA step that you are trying to get into some special company format? But at this point, I think you've probably moved out of the world of PROC REPORT unless you want to rethink how you are producing the report from your data.

Try this code. It uses sashelp.shoes and proc means creates a summary dataset that has _TYPE_=0 as the first obs and the "summary" line:[pre]
ods listing;

proc means data=sashelp.shoes;
class region;
var sales;
output out=work.tot n=salcnt sum=salsum ;
run;

ods pdf file='c:\temp\selvi.pdf';

proc print data=work.tot;
title 'output from proc means';
run;

proc report data=sashelp.shoes nowd
style(summary)={font_weight=bold font_style=roman};
title 'summary report from proc report';
column region sales sales=salcnt sales=salpct;
define region / group;
define sales / n f=comma6. 'Count';
define salcnt / sum;
define salpct / pctsum f=percent9.2 'Percent';
rbreak before / summarize;
rbreak after / summarize;
run;
ods pdf close;
[/pre]
I can make PROC REPORT duplicate the output from PROC MEANS (with a summary at the top.) But, for the PROC REPORT, I am NOT using the output from PROC MEANS, but am having PROC REPORT do for SASHELP.SHOES the equivalent of what PROC MEANS does. PLUS, I can have PROC REPORT calculate the PCTSUM for me, as well AND PROC REPORT can put the summary line both above and below the report lines. So, by rethinking your report, I mean using PROC REPORT for the grouping and summarizing, not just displaying the detail lines.

However, if you feel that you must still produce the report from your data because it is already in the form that you want/need and that you must insert something before the last line on the report, then you may have to consider switching over to the DATA step. [pre]
ods listing;

proc means data=sashelp.shoes;
class region;
var sales;
output out=work.tot n=salcnt sum=salsum ;
run;

proc print data=work.tot;
run;

ods pdf file='c:\temp\selvi.pdf';
data _null_;
set work.tot end=eof;
retain holdreg holdtot holdsum;
file print ods=(variables=(_type_ Region salcnt salsum));
if _type_ = 0 then do;
holdreg = Region;
holdtot = salcnt;
holdsum = salsum;
Region = 'Before';
end;
put @1 _type_ @2 Region @3 salcnt @4 salsum;
if eof then do;
_type_ = 88;
Region = 'Insert';
salcnt = 888;
salsum = 888;
put @1 _type_ @2 Region @3 salcnt @4 salsum;

_type_ = 99;
put @1 _type_ @2 'Repeat' @3 holdtot @4 holdsum;
end;
run;
ods _all_ close;

[/pre]

At this point, I feel that I must be missing something about your data that dictates the approach that you've chosen. Your best bet might be to contact Tech Support so they can look at YOUR real data and help you determine the best method for resolving ALL the issues that you have with your report production. They can help you figure out whether you should stick with PROC REPORT or switch to DATA _NULL_ and can help you with the syntax of both for your specific destination LISTING or PDF.

Good luck!
cynthia
deleted_user
Not applicable
Hi Cynthia,

Thank you. please tell me how to underline the header and overline the summary column in SAS 8.*. The htmlstyle is not working in ODS PDF. I need this underline and overline in ODS PDF. Help me.

Thanks,
Selvi.
deleted_user
Not applicable
Hi ,

I forgot to add one more query. if any one of the analysis variable is missing then my summariaztion should be missing. Is this possible in PROC REPORT. For example:-

ID name sub1 sub2
1 xxx 20 25
1 yyy 14 .
34 .
2 zzz 98 90
2 ccc 67 45
165 135
3 mmm 10 20

Thaks,
Selvi.
Cynthia_sas
SAS Super FREQ
Hi: (answering both questions here)
I have good news and bad news. The bad news is that HTMLSTYLE for simulating headline and overline with PROC REPORT will only work in HTML. HTMLSTYLE does not really work for PDF or RTF -- this is because they are not affected by the kind of CSS style properties and values used by HTMLSTYLE. In SAS 9.2, you will have more choice for the kind of text decoration your choose (with PDF) because there will be bordertop style attributes that you can set separate from borderbottom style attributes. There is also, I believe, an underline style attribute coming in SAS 9.2. Until then, Style=Journal comes closest to giving you a line under the column headers. You might be able to make modifications to the Journal style template to get rid of the line at the bottom of the table, but, even so, I do not believe there is a way in SAS 8 to overline the totals for the PDF destination.

It worries me a bit, however, that you seem to want to do LISTING-like things (HEADLINE/OVERLINE) in non-LISTING destinations -- it goes counter to the purpose of the value added by ODS (which is to make the tabular output from SAS look NOT like the LISTING output). If you really want -EXACTLY- the look and feel of LISTING, but in PDF, you might consider just CREATING a file from the LISTING destination and then use a 3rd party converter to go from ASCII-text format of the LISTING output to PDF format.

Your other choice would be to create HTML output for your report, using HTMLSTYLE and the techniques shown in the Tech Support note and then run a converter to go from HTML to PDF. Many of these converters are freeware, shareware or low cost and you can find them at PlanetPDF or by googling "HTML to PDF converter" or "ASCII to PDF converter".

Now for the good news, it -is- possible to suppress or change what you see at the break if one of the variable values is missing. It'll be a pain if you have a lot of numeric variables. Again, the DATA step might be a better choice for this kind of report because you can choose in the DATA step whether you want to use the SAS sum function tot=sum(tot,var), which will treat the . like 0 or use the regular assignment statement tot = tot + var; where tot will become missing if var is missing.

I do not have SAS 8 available on my machine for testing anymore, so I can't guarantee that this code will work in SAS 8 -- I think it will. If it doesn't, you are getting to the point where Tech Support would be a better resource for you than the forum because they can help you tailor a solution for your particular version of SAS and your report needs.

Good luck!
cynthia [pre]
**** the code;

data selvi;
input ID name $ sub1 sub2;
return;
datalines;
1 xxx 20 25
1 yyy 14 .
2 zzz 98 90
2 ccc 67 45
3 aaa . 50
3 bbb 45 .
;
run;

ods listing;
ods pdf file='c:\temp\chgsum.pdf' style=journal;
proc report data=selvi nowd headline headskip;
column id name sub1 sub2;
define id /order;
define name / order;
define sub1 / sum;
define sub2 /sum;
break after id/summarize;
rbreak after /summarize;
compute before id;
adjsub1 = 0;
adjsub2 = 0;
endcomp;
compute sub1;
if sub1.sum = . then adjsub1 + 1;
if sub1.sum = . then adjend1 + 1;
if upcase(_BREAK_) = 'ID' then do;
if adjsub1 gt 0 then sub1.sum = .;
end;
else if _BREAK_ = '_RBREAK_' then do;
if adjend1 gt 0 then sub1.sum = .;
end;
endcomp;
compute sub2;
if sub2.sum = . then adjsub2 + 1;
if sub2.sum = . then adjend2 + 1;
if upcase(_BREAK_) = 'ID' then do;
if adjsub2 gt 0 then sub2.sum = .;
end;
else if _BREAK_ = '_RBREAK_' then do;
if adjend2 gt 0 then sub2.sum = .;
end;
endcomp;
run;
ods pdf close;

[/pre]
deleted_user
Not applicable
Cynthia,

Thanks, Thanks a lot. It helps me a lot. Actually i am running the ODS PDF Proc report in marco. The PDF has the "The SAS system " statement as in the SASLIST. When i tried outside of macro, PDF created without the above statement. Please help me to remove the statment "The SAS System" from PDF.

Thanks,
Selvi.
Cynthia_sas
SAS Super FREQ
Selvi:
"The SAS System" is the default TITLE for output when you first start a SAS session. In the absence of any other TITLE statements in your code, SAS uses this one, unless/until you turn it off.

To turn off the default title or turn off ANY existing titles, you need a NULL title statement:[pre]
title;[/pre]

To set the title statement, you need:[pre]
title 'My Report';[/pre]

I do not understand why running your code outside of the Macro has an effect on the TITLE statement. The TITLE statements are stored in a global list that is available throughout the session. So if your first program has: title 'My Report'; then EVERY output after that first program will continue to have THAT title until the title is changed with a TITLE statement, or turned off with a null TITLE statement or you start a new session where "The SAS System" is the default title.

Here's an example of using the TITLE statement:[pre]
ods pdf file='c:\temp\title2.pdf';
title 'My Report';
proc print data=sashelp.class;
run;

** this will have the same title as above;
proc print data=sashelp.shoes(obs=5);
run;

** this turns off the title;
title;
data _null_;
set sashelp.class;
file print ods=(variables=(name age height));
put _ods_;
run;

title 'Proc Means Report';
proc means data=sashelp.class;
var height;
run;

** this also turns off the title;
title;
proc freq data=sashelp.class;
tables age;
run;
ods pdf close;
[/pre]

There may be something about how you're running or invoking the macro in a separate SAS session that is using the default TITLE. Perhaps there's an AUTOEXEC or CONFIG file involved that is turning off the title outside of the Macro environment. If inserting a null TITLE statement into your code does not fix the problem, then your best bet is to contact Tech Support.

cynthia
deleted_user
Not applicable
Hi Cynthia,

Thanks, TITLE is working too good. I have few more doubts.

1) Shall we place blank line or line"**" before the sub summary line.
2) Is there any format is availble to display the sign bit (both + & -)?
To implement this, i tried with logic as below.
if sub1 > 0 then sign = "+". And
sign style{just=right} and sub1 style{just=left}.
For this,
i got the coulmn value are together.But the column hearder doesn't came near. i didn't mention any coulmn width and format=5.2;

This is i got. but i need this.
%CHG %CHG
07-06
07-06

-10.40 -10.40

Please help me.

thanks,
Selvi.
Cynthia_sas
SAS Super FREQ
Selvi:
I am glad that the TITLE statement is working for you. There are a few things I don't understand in your post, but let me address your questions first.

1) Shall we place blank line or line"**" before the sub summary line.
As I explained before, anything that you write with the LINE statement will come AFTER any summary line produced by PROC REPORT. Therefore, it is NOT possible for any text or blank line to be placed BEFORE the PROC REPORT summary line. That is why I posted the example of the DATA step program. Starting in SAS 9.2, you would be able to put the equivalent of an OVERLINE above the summary using new style attributes, but if you wanted to place any other text ABOVE the PROC REPORT summary line, this is just not possible.

2) Is there any format is availble to display the sign bit (both + & -)?
You can do this with a PICTURE format...something like this:
[pre]
proc format;
picture plusmin
. . . code for picture format . . .
run;
[/pre]

(The reason I did not include the code for the picture format is that some combination of the characters needed for the format displayed a line of ]]]]]] in the forum preview window. If you send an email to cynthia.zender@sas.com, I will send you the picture format code.)

Once you define the picture format, you would USE that picture format in your DEFINE Statement:
[pre]
DEFINE numvar / sum f=plusmin.;
[/pre]

You also said that:
"To implement this, i tried with logic as below.
if sub1 > 0 then sign = "+". And
sign style{just=right} and sub1 style{just=left}."

It looks to me like you are trying to make a separate column for the + sign, but you don't need to do this at all if you are using the picture format.

Also, I would not expect the syntax of the style= override to work. This is wrong:[pre]style{just=right}[/pre]

As I explained, there are several different ways to write a style= override:
#1 and #2 use style(component)={attr=value} syntax and the rest use some form of style={attr=value}:
1) the PROC REPORT statement[pre]
proc report data=whatever nowd
style(header)={background=pink};
[/pre]

2) the DEFINE statement[pre]
define numvar / sum f=dollar14.
style(header)={just=r background=green}
style(column)={just=l};
[/pre]

3) the BREAK or RBREAK statement[pre]
break after breakvar / summarize
style={background=yellow};
[/pre]
4) the CALL DEFINE statement[pre]
if var = 'something' then
call define(_COL_,'style','style={just=l}' ) ;
[/pre]

5) the COMPUTE BEFORE/AFTER[pre]
compute after breakvar/
style={font_weight=bold};
line 'This is AFTER the summary line';
endcomp;[/pre]
I don't know what you mean by this statement:
"For this, i got the coulmn value are together.But the column hearder doesn't came near."

If you created a separate column to hold a SIGN value (either + or -) then it would be very hard to get the column header for SIGN and the column header for the SUB variable together. I think using the picture format is the best choice.

I'm also having a hard time with this part of your post:
"This is i got. but i need this.
%CHG %CHG

07-06 07-06
-10.40 -10.40"

Is %CHG a column header??? It looks to me like you're got 4 columns on line 2 (what is 07-06 07-06 -- 2 columns or 4 columns)?? and only 2 columns on line 1 (%CHG %CHG) and line 3 (-10.40 -10.40). If this is something that appears at the summary line and not in the column headers, you cannot insert text before the summary line.

In my opinion, your best bet for this report really is to contact Tech Support. They can open a track, look at your data, look at your code and keep the track open until ALL your questions are resolved. Since you are working on ironing out the capabilities of PROC REPORT versus DATA step, it really is to your advantage to contact Tech Support. You keep indicating that you need to insert a LINE before the PROC REPORT summary -- but, you CANNOT insert a LINE before the PROC REPORT summary line. Therefore, you may have to either restructure the report or switch to DATA step for this report. All your questions seem to be centered on this one report, and Tech Support can really pin down what you need to do and help you come up with the best solution -- whether it's PROC REPORT or DATA step.

cynthia
Cynthia_sas
SAS Super FREQ
Here is the PROC FORMAT code for the PICTURE format. If you cut and paste the code, there is a possibility that you might notice
&-lt-; (ampersand-LT-semi-colon) in the code -- if you do, then that combination of letters should be replaced with the less than symbol. (It didn't happen for me in Notepad, but could happen in other editors.)
[pre]
proc format;
picture plusmin low -< 0 = '0,009.99' ( prefix = '-' )
0 <- high = '0,009.99' ( prefix = '+' )
;
run;
[/pre]

You would use the format PLUSMIN in a FORMAT statement:

format numvar plusmin.;
or a DEFINE statement:
define numvar /sum f=plusmin.;


cynthia
deleted_user
Not applicable
Hi Cynthia,

Thanks a lot. Really good on behalf of your response. Beacuse of your replies i have learnt more in SAS. It will also help me for future. I appreicate your response.

The Picture statement in PROC format is wooking good. And also the less than symbol is seen to us. If you give your contact number, it will be good for me.

Thanks,
Selvi.
Cynthia_sas
SAS Super FREQ
Selvi:
I'm glad that you're learning a lot about SAS. I think that SAS is very cool, too and I have fun with it. I am constantly amazed at the business/information problems that can be solved using SAS.

If you want to contact me, my email is cynthia.zender@sas.com -- although I am about to be out of the office for training committments and the SAS Global Forum. So, if you need immediate help with your report, you should contact Tech Support.

cynthia

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 15 replies
  • 1203 views
  • 0 likes
  • 3 in conversation