All ODS and Base Reporting posts

Re: ODS PDF image resolution

ballardw — Thu, 22 Jul 2021 14:41:04 GMT

You don't show where you might be attempting to use any image resolution setting so it is hard to make pointed suggestions.

Generally the image settings such as DPI in SAS only relates to graphs created by SAS. Are you referencing an image made by something else and expecting SAS to re-build the image to a different resolution?

Also, does your log include any notes or messages about image resolution and/or memory constraints when creating/using the images?

ODS PDF image resolution

JacobC — Thu, 22 Jul 2021 14:26:08 GMT

Relatively new to SAS and having issues with resolution of PNG and JPG images when they are exported to PDF reports.

Have tried using the suggestions I was able to find on this forum and elsewhere but still having issues with resolution in the finished product.

Image is crisp in both png and jpg but when using the following, the image ends up pixelated on the PDF. It does not happen all of the time depends on the image being used.

%let Image='Drive:\folder\folder\folder/LOGO_1.PNG' dpi=300;

Same problem here

ODS LAYOUT ABSOLUTE;

ODS Region Y=0.1in X=0.1in;
ODS text= "~{style[preimage='Drive:\folder\folder\folder/LOGO_1.PNG']}";
ODS Region Y=0.38in X=2.5in;
ODS text= "~{style[color=BL Font_size=16pt Font_weight=bold]&ANAME}";
ODS text= "~{style[color=BL Font_size=16pt Font_weight=bold]Report name}";
ODS text= "~{style[color=BL Font_size=16pt]&monthfull &Y}";

Any suggestions would be appreciated.

Re: Centering Text Using ODS TAGSETS.RTF

jackieme — Wed, 21 Jul 2021 17:13:19 GMT

Thank you for your help and linking the reference papers. This solved my issue!

Re: Centering Text Using ODS TAGSETS.RTF

Cynthia_sas — Wed, 21 Jul 2021 14:20:37 GMT

Hi:

You need to make a STYLE template change with PDF and RTF destinations to alter the justification of the ODS TEXT strings in the output. The style element that changes the ODS TEXT value is the USERTEXT style element. Here's an example, compared to changing title:

You'll find more about changing style templates in this paper https://support.sas.com/resources/papers/proceedings10/033-2010.pdf and about templates in general in this paper: https://support.sas.com/resources/papers/proceedings09/227-2009.pdf ..

Hope this helps,

Cynthia

Re: Centering Text Using ODS TAGSETS.RTF

ballardw — Wed, 21 Jul 2021 14:21:52 GMT

You have to tell SAS how wide the text display area is. Add a Leftmargin= 1in and Rightmargin=1in or similar to the ODS Text. Pick your measurement as needed and you can use units of IN (inches) CM (centimeters) . There are other units but this are pretty easily understood.

Centering Text Using ODS TAGSETS.RTF

jackieme — Wed, 21 Jul 2021 13:42:57 GMT

I am trying to center text in ods tagsets.rtf output, but the text is still left justified even with j=c.

ods escapechar="^";

ods tagsets.rtf file="&outpath.\table1.rtf";

ods text="^S={j=c fontsize=13pt fontweight=bold}First text on page.";

ods tagsets.rtf close;

Thanks!

Re: ODS EXCEL AND HOW TO INSERT AN IMAGE

RJB_NZ — Wed, 21 Jul 2021 00:00:59 GMT

I would like to see the combination of R and SAS solution, but the link doesn't work, and there's not enough information about how to find it on the Georgia University website (if it's still there...)

Re: Controlling borders in PROC REPORT and ODS Excel

bstarr — Tue, 20 Jul 2021 21:46:33 GMT

Yes that is perfect, thank you!

Re: Controlling borders in PROC REPORT and ODS Excel

Rydhm — Tue, 20 Jul 2021 21:13:34 GMT

Since it appears to be a conditional border, that can be done by adding a new variable prior to reporting. This variable is set to 1 for the last entry of each group. See code below:

data test;
set sashelp.cars (keep=Make Model MSRP MPG_City MPG_Highway);
    where Model in (' A4 1.8T 4dr' ' 325i 4dr');
    MPG = MPG_City; output;
    MPG = MPG_Highway; output;
run;
data test2;
set test (in=a) test (in=b);
    if a then year = '2019';
    if b then year = '2020';
run;

PROC SORT data=test2;
BY Make year;
RUN;

DATA test3;
SET test2;
BY Make year NOTSORTED;
IF LAST.Make or last.year THEN rowline=1;
RUN;

ods excel file="H:\Sample Proc Report.xlsx";
proc report data=test3 nowd spanrows style(hdr)=[fontfamily="Arial" fontsize=9pt foreground=white background=darkblue just=c] 
    style(lines)=[fontfamily="Arial" fontsize=9pt just=c borderbottomwidth=0 borderbottomstyle=hidden bordertopwidth=0 bordertopstyle=hidden]
    ;
    title "Title";
    columns ("Header1" Year Make ("Header2" MPG) MSRP) rowline;
        define Year / "Year" group order=data style(column)=[cellwidth=.65in];
        define Make / "Make" group order=data style(column)=[cellwidth=.4in];
        define MPG / "MPG" display style(column)=[cellwidth=.65in borderbottomcolor=white bordertopcolor=white] ;
        define MSRP / "MSRP" group style(column)=[cellwidth=.65in];
		DEFINE ROWLINE / ANALYSIS NOPRINT;
	COMPUTE rowline;
		 if rowline.sum=1 then
		 CALL DEFINE (_row_,'style',
		 'style=[borderbottomcolor=lightgrey' ) ;
	ENDCOMP;
	
    compute after Make / style=[bordertopcolor=black bordertopwidth=1pt bordertopstyle=solid borderbottomcolor=black borderbottomwidth=1pt borderbottomstyle=solid];
        call define(_row_,'style','style=[bordertopstyle=solid bordertopwidth=1pt bordertopcolor=black borderbottomstyle=solid borderbottomwidth=1pt borderbottomcolor=black]');
    endcomp;
    compute after/style=[just=l fontfamily="Arial" color=black backgroundcolor=white borderbottomstyle=hidden borderleftstyle=hidden borderrightstyle=hidden];
        line "Footer goes here.";
    endcomp;
run;
ods excel close;

Re: Controlling borders in PROC REPORT and ODS Excel

bstarr — Tue, 20 Jul 2021 19:08:39 GMT

Thanks for referencing that question/solution. Unfortunately, that code marked as a solution does not appear to work, at least in my version of SAS (9.4 TS1M6). It throws errors, and when I fix some of the errors, it still trips up saying that the subroutine "style" is unknown. The next logical leap I can think of is to use call define style, which I've already tried.

Re: Controlling borders in PROC REPORT and ODS Excel

Rydhm — Tue, 20 Jul 2021 18:40:17 GMT

your problem seems similar to this one:
https://communities.sas.com/t5/ODS-and-Base-Reporting/BORDER-ods-Excel-Proc-report-break-after-column/td-p/453471

Try the above solution.

Re: Frame webpage not working with ods destination as html and table of contents webpage not working

Cynthia_sas — Tue, 20 Jul 2021 17:14:35 GMT

Hi:

I do not have the issue you report when I run a version of your program on my SAS OnDemand account. As you can see below, I am using a slightly modified version of your code to use SASHELP datasets to test the frame navigation file creation with ODS HTML:

After my code runs, I then need to download all 3 of the files to my local machine. To keep things easy for testing, I just downloaded all 3 files to my local C: drive Downloads folder, as shown below: I did not use your data or your file names.

Next I right-clicked on the FRAME.HTML file and clicked to open with Google Chrome.

All 3 files, BODY.HTML, TOC.HTML and FRAME.HTML must be in the same location folder when downloaded (and you can see them all in my Downloads folder). When Google Chrome opens, I see the normal FRAME displayed with the TOC.HTML file on the left panel and the BODY.HTML file displayed on the right side, as shown below:

If you are going to move all 3 files to a web server, then keep in mind that all the files will need to be stored in the same folder on the server.

If you continue to have issues with your frames and ODS HTML, you might need to open a track with SAS Technical Support.

Cynthia

Controlling borders in PROC REPORT and ODS Excel

bstarr — Tue, 20 Jul 2021 17:12:54 GMT

Hello,

I am attempting final formatting on reports generated using PROC REPORT and ODS Excel. I am trying to remove borders that appear between two rows from grouped data, like this:

Sample code:

data test;
set sashelp.cars (keep=Make Model MSRP MPG_City MPG_Highway);
    where Model in (' A4 1.8T 4dr' ' 325i 4dr');
    MPG = MPG_City; output;
    MPG = MPG_Highway; output;
run;
data test2;
set test (in=a) test (in=b);
    if a then year = '2019';
    if b then year = '2020';
run;

ods excel file="C:\Sample Proc Report.xlsx";
proc report data=test2 nowd spanrows style(hdr)=[fontfamily="Arial" fontsize=9pt foreground=white background=darkblue just=c] 
    style(lines)=[fontfamily="Arial" fontsize=9pt just=c borderbottomwidth=0 borderbottomstyle=hidden bordertopwidth=0 bordertopstyle=hidden];
    title "Title";
    columns ("Header1" Year Make ("Header2" MPG) MSRP);
        define Year / "Year" group order=data style(column)=[cellwidth=.65in];
        define Make / "Make" group order=data style(column)=[cellwidth=.4in];
        define MPG / "MPG" display style(column)=[cellwidth=.65in /*borderbottomstyle=hidden bordertopstyle=hidden*/];
        define MSRP / "MSRP" group style(column)=[cellwidth=.65in];
    compute after Make / style=[bordertopcolor=black bordertopwidth=1pt bordertopstyle=solid borderbottomcolor=black borderbottomwidth=1pt borderbottomstyle=solid];
        call define(_row_,'style','style=[bordertopstyle=solid bordertopwidth=1pt bordertopcolor=black borderbottomstyle=solid borderbottomwidth=1pt borderbottomcolor=black]');
    endcomp;
    compute after/style=[just=l fontfamily="Arial" color=black backgroundcolor=white borderbottomstyle=hidden borderleftstyle=hidden borderrightstyle=hidden];
        line "Footer goes here.";
    endcomp;
run;
ods excel close;

I've commented out a portion on the "define MPG" row that when uncommented, removes ALL borders for that column (but the borders don't reappear in my "compute after Make" block). So I can either remove all row borders for that column, or leave all row borders in - I can't seem to remove those alternating row borders.

What am I missing?

Thank you,

Brian

Re: ODS TAGSETS.EXCELXP Loading 17 digits length number , truncating or change the last 3 digits

Inp — Sun, 18 Jul 2021 01:42:55 GMT

Thanks very much . That resolved my problem.

Thanks and Regards,

Inp

Re: ODS TAGSETS.EXCELXP Loading 17 digits length number , truncating or change the last 3 digits

KurtBremser — Sat, 17 Jul 2021 18:04:24 GMT

The loss of precision happens here:

X = 12345678901234567;

Anytime you cram such a number into a SAS variable, it happens.

Store it as a string in the first place:

X = "12345678901234567";

Re: ODS TAGSETS.EXCELXP Loading 17 digits length number , truncating or change the last 3 digits

Inp — Sat, 17 Jul 2021 17:32:49 GMT

Thanks very much for your help,

Even I changed this X value to y as charactor , but it doesn't shows as charactor and truncate as numeric value. Is there any way to change this y to charactor where I can load to ODS TAGSETS.EXCELXP. Should I have to put any style where it can change as Charactor? . ? If any ,can you please tell how?

DATA TEMP1;
length y $16;

X = 12345678901234567;

y=put(x,z16.);

RUN;

FILENAME TEMP 'C:\TMP\TEST.XLS';

ODS _ALL_ CLOSE;
ODS TAGSETS.EXCELXP FILE=TEMP STYLE=STYLES.HTMLBLUE RS=NONE;
ODS TAGSETS.EXCELXP OPTIONS(SHEET_NAME="MATCH"

EMBEDDED_TITLES='YES'
AUTOFILTER='ALL' AUTOFIT_HEIGHT='YES' MINIMIZE_STYLE='YES'
ABSOLUTE_COLUMN_WIDTH='30'
ORIENTATION='LANDSCAPE' SCALE='75');

RUN;

TITLE 'TESTING AS OF MAY2021';

PROC REPORT DATA=TEMP1 NOWD SPLIT='*' ;
COLUMN y;
DEFINE y / 'ACCOUNT' format=$16.;

RUN;

ODS _ALL_ CLOSE;
ODS LISTING;`

Re: ODS TAGSETS.EXCELXP Loading 17 digits length number , truncating or change the last 3 digits

KurtBremser — Sat, 17 Jul 2021 16:55:51 GMT

You run into the limits of numeric precision caused by the finite amount of binary digits used to store decimal numbers. Because of this, SAS (and any software that uses 64 bits to store numbers in floating point format) can "only" handle up to 15 (and for some numbers, 16) decimal digits without loss of precision.

For the field of statistics, this poses no problem.

If that "number" is in fact some kind of code, store it as character.

ODS TAGSETS.EXCELXP Loading 17 digits length number , truncating or change the last 3 digits

Inp — Sat, 17 Jul 2021 13:46:37 GMT

I am trying to loading 17 digits length number from sas table to ODS TAGSETS.EXCELXP ( using SAS94) . but it is messing up the last 3 digits of the number. Can any one please let me know the solution to my problem. Here is my sample coding .

DATA TEMP1;
X = 12345678901234567;
RUN;

FILENAME TEMP 'C:\TMP\TEST.XLS';

ODS _ALL_ CLOSE;
ODS TAGSETS.EXCELXP FILE=TEMP STYLE=STYLES.HTMLBLUE RS=NONE;
ODS TAGSETS.EXCELXP OPTIONS(SHEET_NAME="MATCH"

EMBEDDED_TITLES='YES'
AUTOFILTER='ALL' AUTOFIT_HEIGHT='YES' MINIMIZE_STYLE='YES'
ABSOLUTE_COLUMN_WIDTH='30'
ORIENTATION='LANDSCAPE' SCALE='75');

RUN;

TITLE 'TESTING AS OF MAY2021';

PROC REPORT DATA=TEMP1 NOWD SPLIT='*' ;
COLUMN X;
DEFINE X / 'ACCOUNT'
style={tagattr='format:00000000000000000'};
RUN;

ODS _ALL_ CLOSE;
ODS LISTING;

Re: SAS Stored process download a large file as excel

Quentin — Fri, 16 Jul 2021 12:34:33 GMT

Please see my answer to this similar question, which has an example of streaming the file using data _null_ step.

https://communities.sas.com/t5/SAS-Stored-Processes/webout-with-Proc-Export/td-p/393351

Re: SAS Stored process download a large file as excel

PRAVIN_JAIN — Fri, 16 Jul 2021 12:28:08 GMT

I am able to export using PROC EXPORT and save to SAS server. How can we download this file to local machine if we don't use ODS, any alternative ?

Re: SAS Stored process download a large file as excel

Quentin — Fri, 16 Jul 2021 12:14:10 GMT

This is likely and ODS EXCEL issue, not an issue with the downloading.

What version of SAS are you on? I'd review this thread: https://communities.sas.com/t5/SAS-Programming/ODS-EXCEL-out-of-memory-error-with-dataset-containing-25-rows/td-p/617960

ODS excel is great for fancy formatting, but if you're not doing anything fancy, you might be better off with PROC EXPORT or other approaches that don't have the same memory footprint.

SAS Stored process download a large file as excel

PRAVIN_JAIN — Fri, 16 Jul 2021 12:06:09 GMT

We have a stored process which gives excel report to users. This get stuck when we try to download a large dataset having around 500 K observations due to insufficient memory.

I am using below code, is there a way we can download such a large file using ODS.

data _null_;
rc = stpsrv_header('Content-type','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
rc = stpsrv_header('Content-disposition',"attachment; filename=test.xlsx");
run;

ods excel file = _webout style=Plateau;
libname in_lib '/data/users/report/';

proc print data=in_lib.trans_detail;
run;
ods excel close;

Re: Sending emails from SAS with embedded table from dataset

MarieT — Thu, 15 Jul 2021 15:41:10 GMT

Thank you very much, Cynthia!

That works!

Re: Sending emails from SAS with embedded table from dataset

Cynthia_sas — Thu, 15 Jul 2021 15:25:42 GMT

Hi:
Please take a look at this user group paper: https://support.sas.com/resources/papers/proceedings11/101-2011.pdf -- in it you'll see that the author had to work with the team at his company to get things working. You need to work with someone AT YOUR COMPANY to get this working. There's not any way for someone on the Forum to know what your settings at your company need to be. And no one on the Forum has any permissions to alter the configuration files needed by SAS at your company.
Cynthia

Re: Sending emails from SAS with embedded table from dataset

MarieT — Wed, 14 Jul 2021 19:56:33 GMT

Thank you, Cynthia !
Without knowing the EMAILHOST I can’t send this kind of email, or there are some other options please ?

Re: Sending emails from SAS with embedded table from dataset

Cynthia_sas — Wed, 14 Jul 2021 19:51:40 GMT

Hi:
That information needs to come from your email administrator and your SAS administrator needs to know the value for EMAILHOST so they can use it in the configuration files for SAS.
Cynthia

Re: Sending emails from SAS with embedded table from dataset

MarieT — Wed, 14 Jul 2021 16:59:10 GMT

Than you, Cynthia !

Where can I found the email host? Is it in my log ?

Re: Sending emails from SAS with embedded table from dataset

Cynthia_sas — Wed, 14 Jul 2021 16:14:56 GMT

Hi:
If the email host cannot be found, then that means you need to find out from your mail administrator what the correct host name is. You'll also need to check with your SAS Administrator to make sure that the email-related system options refer to the correct settings. If you need more help, your best resource is SAS Tech Support.
Cynthia

Re: Generating native .XLS files with ODS Excel

KurtBremser — Wed, 14 Jul 2021 14:37:16 GMT

ODS EXCEL can only create XLSX files. The XLS format is completely different, and needs (at least in the case of SAS) MS-supplied, Windows-only modules to create/access them.

Up to the introduction of ODS EXCEL, ODS TAGSETS.EXCELXP was the way to go.

An application that has not been maintained for decades (XLSX was introduced in 2007) may not be dead yet, but it sure smells funny.

Re: Generating native .XLS files with ODS Excel

Reeza — Wed, 14 Jul 2021 14:35:49 GMT

Not besides PROC EXPORT unfortunately but I'm assuming you're using ODS EXCEL for some formatting/styling. But that also doesn't make a ton of sense if it's going into another application which implies a data feed...

Re: Generating native .XLS files with ODS Excel

aandrewn — Wed, 14 Jul 2021 14:30:42 GMT

Thanks Tom - I generally agree but was/am hoping there is something. Ideally our systems get upgraded but there doesn't seem to be an immediate interest in that either.

Re: Generating native .XLS files with ODS Excel

Tom — Wed, 14 Jul 2021 14:27:10 GMT

I don't think you can do this and I doubt that SAS will spend any time developing something to produce an old (and proprietary) format.

Generating native .XLS files with ODS Excel

aandrewn — Wed, 14 Jul 2021 14:24:52 GMT

Hi all, I know the ODS Excel destination allows for native .XLSX files, but is there a way to generate older .XLS files using ODS Excel?

I am working with an old system/application that only allows for XLS files. I know there is proc export and other options to output as an .XLS however I need the functionality of ODS to get the formatted output I'm looking for. Also I don't want to tagsets - I have a macro that uses DDE to open the .xml file and save it as a true .xls file, however this is not reliable in a batch environment. Hoping there is an argument/parameter I'm not familiar with when using ODS Excel.

Thanks!

Re: Sending emails from SAS with embedded table from dataset

MarieT — Wed, 14 Jul 2021 10:25:56 GMT

Hello,
When I run this code I have an ERROR : ERROR: Email host xx.xx.xx not found.
Could you help me please with this option ?

Thank you !

Frame webpage not working with ods destination as html and table of contents webpage not working

rhafsa — Wed, 07 Jul 2021 18:18:48 GMT

Hi there,

I am working on this program that I have written. The program is to create a table of contents webpage with ods destination as html.

When I run this program, I do get a downloadable file with the names of the html webpages that I have created.

A couple of things do not run correctly from this program.

First, the frame webpage contains the body (output of print and means procedures) and the table of contents.

When I click on the table of contents meaning the links associated with the output of the procedure..it is giving me a blank page.

Second, when I open the contents webpage, the table of contents are displayed correctly however when I click one each link to see the output of each procedure..it is showing me outputs of both procedures. This is not correct, because I should see the output of print procedure only when I click the print procedure link in the table of contents webpage..same goes for the other link.

If anyone can help me in figuring out the solution..much appreciated. The name of the data set is called test_scores which is saved in a library named marchlib.

I am following program 19.2 (pg. 327) from the textbook named learning sas by example 2nd edition..

The program is below:

ods listing close;

*create table of contents with ods html statement and options;

ods html body='test_scores_Body.html'
contents = 'test_scores_Contents.html'
frame = 'test_scores_Frame.html'
path = "/home/u58138201/Practice/junelib_folder" (url=none);

*define proc print procedure;
title "Listing of Test_Scores";
title2 "Sample of HTML Outputs-all defaults";

proc print data=marchlib.test_scores;
id ID;
var Score1-Score3;
run;

*define proc means procedure;
title "Descriptive Statistics for Test_scores dataset";
proc means data=marchlib.test_scores n nmiss mean min max maxdec=2;
var Score1-Score3;
run;

*close html tag;
ods html close;
ods listing;

Thanks.

Re: When is ods path .... (write)?

Cynthia_sas — Tue, 29 Jun 2021 18:24:02 GMT

Hi:
And to add more info to 's response: You should NEVER attempt to define SASHELP.TMPLMST as WRITE or UPDATE in an ODS PATH statement, since that is the item store delivered with each SAS install depot. If you're lucky, you won't ruin the template store because your administrators made it read only. If you're unlucky, you can accidentally render SASHELP.TMPLMST unusable.
Cynthia

Re: When is ods path .... (write)?

xxformat_com — Tue, 29 Jun 2021 17:24:47 GMT

Thank you Kurt

Re: When is ods path .... (write)?

KurtBremser — Tue, 29 Jun 2021 16:13:26 GMT

You use WRITE when you want to make sure that the template store is re-initialized, e.g. when a lot of cruft has accumulated in it

Re: When is ods path .... (write)?

data_null__ — Tue, 29 Jun 2021 14:52:41 GMT

wrote:

I was not looking for a copy of the sas online documentation.

Interesting comment. Not here to make friends?

WRITE

provides Write access (always creating a new template store) as well as Read access.

Re: When is ods path .... (write)?

xxformat_com — Tue, 29 Jun 2021 14:14:50 GMT

I was not looking for a copy of the sas online documentation.

Re: When is ods path .... (write)?

Athenkosi — Tue, 29 Jun 2021 13:57:13 GMT

The ODS PATH statement is used to manage the template stores, for reading and saving templates.

Template store can have one of three access modes: read, write and update.

READ: You can only read templates to a template store

UPDATE: You can read or write templates to a template, if the template store does not exist a new one is created.

WRITE: You can read or write templates to a template, a new template store is always created

When is ods path .... (write)?

xxformat_com — Tue, 29 Jun 2021 13:46:19 GMT

Hi,

Can you think of an example where ods path ....(write) is used ? ods path ....(read/update) usually do what I need.

Regards,

Re: Align Top in Proc tabulate

Cynthia_sas — Tue, 22 Jun 2021 00:35:18 GMT

Hi: Sometimes to see the impact of verticalalign, you need to force the headers and rows to be tall, as shown in #1 below:

So then you can see that for TALL header cells and rowheader cells the default alignment in the vertical direction is TOP. But for data cells, the default vertical alignment is BOTTOM.

That can be changed with overrides as shown in #2. My code builds on the previous code, with tall column headers and tall rows to show the impact of the overrides.

Cynthia

Here's the full code:

proc tabulate data=sashelp.class;
   title '1) default alignment is Top for tall header cells and bottom for tall data cells';
   class sex;
   classlevels sex/style=[height=.75in];
   var height;
   table sex,
         height='This is the Height' all='Total'
     /box={label='This is a long text string that will make the column headers tall' style={width=1.5in}};
  keylabel sum=' ' n=' ';
run;

proc tabulate data=sashelp.class;
   title '2) change alignment for header and data cells';
   class sex /style=[just=C];
   classlevels sex/style=[verticalalign=m just=c height=.75in];
   var height /style={verticalalign=M};
   table sex,
         sum*height='This is the Height' n*all*{style={verticalalign=m}}
     /box={label='This is a long text string that will make the column headers tall' style={width=1.5in}};
  keylabel sum=' ' n=' ' all='Total';
  keyword all / style={verticalalign=B};
run;

Re: Align Top in Proc tabulate

ballardw — Mon, 21 Jun 2021 23:03:36 GMT

First thing I see is that you would have to have some other variable column heading creating a very tall cell to begin with and there isn't anything in your code that does that. Proc Tabulate for such short text values as C1 through C4 and Total, which would be the visible column headings, isn't going to make any cells high enough for a vertical alignment to be visible or to have the upper left box that tall either.

So, you need to show what is making those cells that tall in the first place.

For example:

proc tabulate data=sashelp.class;
   class sex;
   classlevels sex/style=[verticalalign=C];
   var height;
   table height,
         sex=' ' all='Total'
   ;
run;

None of the column headings would have the 5 or 6 "row height" appearance you show. So there is something left out.

Re: Align Top in Proc tabulate

sasuser_sk — Mon, 21 Jun 2021 22:47:49 GMT

I am sorry but I mostly get confused while asking questions and do no represent my question well. Here is my code with ODS statement and example of my output. I want to align C1,C2,C3 to the top as currently they are in the bottom of the cell. I do not know how to show data in this case but hope that output picture helps!. Thank you!

ods excel file= "&SAS_Folder.&ReportFileName." options (sheet_interval='table' sheet_name='Gross Loads' suppress_bylines='on' embedded_titles='ON' embedded_footnotes='ON');

title j=l "Weekly Loads - Q1 2021" ;

footnote j = l "Generated by the SAS System (&_SASSERVERNAME, &SYSSCPL) on %TRIM(%QSYSFUNC(DATE(), NLDATE20.)) at %TRIM(%SYSFUNC(TIME(), TIMEAMPM12.))";

PROC TABULATE DATA=WORK.FINAL_RESULT f=10.2 S=[foreground=black just = c vjust=c] ;

VAR Acct_ID;

CLASS "Group of Deals"n;

CLASS Week;

classlevels Week/style = {font_weight=bold width=1.5in};

classlevels " Group of Deals"n /style = {font_weight=bold width=1in just =c };

TABLE /* Row Dimension */

Week= '' all='Total',

/* Column Dimension */

Group of Deals'n = ''*(Acct_ID=''*N='') All=''*n='Total' ;

RUN;

RUN; QUIT; title;footnote;

Re: Align Top in Proc tabulate

ballardw — Mon, 21 Jun 2021 20:18:01 GMT

WHICH column? You don't show a VJUST anywhere so you should show that as well.

Which is you active ODS style? and which ODS destination?

We may need example data to test code.

You may want VERTICALALIGN, not VJUST.

Getting any

Align Top in Proc tabulate

sasuser_sk — Mon, 21 Jun 2021 19:32:16 GMT

Hello,

I have proc tabulate below and want to align my column to top. I have tried using vjust=T but that does not work. Maybe I am not putting vjust in the right place. Can someone please help here. Thank you!

Proc Tabulate DATA=WORK.FINAL_RESULT f=10.2 S=[foreground=black just = c] ;

VAR Acct_ID;

CLASS "Group of Deals"n;

CLASS Week;

classlevels Week/style = {font_weight=bold width=1.5in};

classlevels "Group of Deals"n /style = {font_weight=bold width=1in just =c };

TABLE /* Row Dimension */

Week= '' all='Total',

/* Column Dimension */

'Group of Deals'n = ''*(Acct_ID=''*N='') All=''*n='Total' ;

RUN;

Re: Unicode Characters being ignored in report output

r_behata — Wed, 16 Jun 2021 01:46:16 GMT

Awesome!! Thanks for pointing out the ODS ESCAPECHAR.

Re: Unicode Characters being ignored in report output

ballardw — Tue, 15 Jun 2021 22:49:43 GMT

Are you sure the ODS Escapechar is set? To the ^ character?

Unicode Characters being ignored in report output

r_behata — Tue, 15 Jun 2021 22:01:13 GMT

I am trying to re-use an existing format which includes some Unicode Characters in it. However, The report which I intend to use this format (HTML Output) does not resolve the unicode characters in a desired format. The other legacy report that uses the same format is displaying the resolved values.

Wondering if there is any option to enforce the unicode characters in the report.

Here is the sample code :

proc format;
	value $mtrcspc
		New_Acct_EOM_Purch_Amt="New Acct (^{unicode 2264}12 MOB) Month-end Purchase Sales (in MM)"
		New_Acct_EOC_Purch_Pct="% New Acct (^{unicode 2264}12 MOB) Month-end Sales";
run;

data have;
col1="New_Acct_EOM_Purch_Amt";output;
col1="New_Acct_EOC_Purch_Pct";output;
run;


proc print data=have;
	var col1;
	format col1 $mtrcspc.;
run;

Thank You.

Re: Different data sets in multiple excel sheet

Reeza — Tue, 15 Jun 2021 17:15:30 GMT

PROC FREQ doesn't let you control that unfortunately. If you want to control your output you can switch to PROC TABULATE instead - but that also means you need to specify more options to get the same output.

Re: Different data sets in multiple excel sheet

dustychair — Tue, 15 Jun 2021 16:56:51 GMT

Thank you so much! I noticed that I missed two semicolons besides writing Comprehension twice. Last question: how can make percentages one digit instead of two using ods excel?

Thank you!

Re: Different data sets in multiple excel sheet

Reeza — Tue, 15 Jun 2021 14:28:34 GMT

You have comprehension listed twice....
Run the macro with "options mprint symbolgen;" before the code and then check your log.

Re: Can I assign a style by using a "class" defined in Proc Template?

PhilC — Tue, 15 Jun 2021 14:27:20 GMT

I found this paper: SUGI 26: Changes & Enhancements for ODS by Example (through V8.2) (sas.com)

This article: Solved: Change font size for all "PROC ODSTEXT" text - SAS Support Communities

Re: Can I assign a style by using a "class" defined in Proc Template?

PhilC — Tue, 15 Jun 2021 13:37:55 GMT

Thanks, and sorry, I complain quietly about the inability of others to write proper questions, and then I fail to do so myself.

Re: Can I assign a style by using a "class" defined in Proc Template?

PhilC — Tue, 15 Jun 2021 13:27:00 GMT

Forgive me, I was not clear, I am editing the question. Thanks for your patience.

Re: The REPORT procedure: Formatting a row based on the value of one cell in that row.

mftuchman — Tue, 15 Jun 2021 01:48:28 GMT

Re: How to highlight specific text with color at ODS PDF output?

jimbarbour — Tue, 15 Jun 2021 01:17:18 GMT

Thank you, . It's about time somebody showed up around here who actually knows what they're talking about...

Jim

Re: How to highlight specific text with color at ODS PDF output?

Cynthia_sas — Tue, 15 Jun 2021 01:15:00 GMT

Hi:
Yes, 's choice b is the answer. I always explain it to my students this way when they ask if something is possible (like can I change the page number to the bottom of the page) -- The answer is "It depends on the destination" -- RTF and PDF -- yes; HTML -- No, ODS Excel, yes, but you have to use Microsoft specific field codes to do it.

The way I understand PDF is that PDF "wants" the background color to apply to the entire data cell. So foreground color for part of the string is OK. But background color doesn't work for inline formatting because PDF wants the whole cell to have the same background. It's different for RTF and Word because, well, Microsoft.

Cynthia

Re: Different data sets in multiple excel sheet

dustychair — Mon, 14 Jun 2021 23:47:01 GMT

Thank you so much for taking care of my question. This code is doing something weird. It is saving them in the excel file but the name of the file and content of the file doesn't match. For example file's name is comprehension but there are literacy results inside. You can find what I have done.

%macro intd (s);
ods excel file="C:\Projects\pl_by_cluster_&s..xlsx" options(sheet_interval='proc') style=meadow;

data a501_&s._main_inc;
set a501_&s._main_inc;
&s._pl_n=int(&s._pl);
run;

proc sort data=a501_&s._main_inc;
by cluster_&s._n;
run;

proc freq data=a501_&s._main_inc;
table &s._pl_n;
by cluster_&s._n;
ods output OneWayFreqs=pl_by_cluster_&s.
run;

proc sort data=a501_&s._main_inc;
by grade;
run;

proc freq data=a501_&s._main_inc;
table &s._pl_n;
by grade;
ods output OneWayFreqs=pl_by_grade_&s.
run;
ods excel close;
%mend;
%intd (comprehension);
%intd (listening);
%intd (reading);
%intd (speaking);
%intd (writing);
%intd (oral);
%intd (literacy);
%intd (comprehension);
%intd (overall);

Re: The REPORT procedure: Formatting a row based on the value of one cell in that row.

Athenkosi — Mon, 14 Jun 2021 22:58:04 GMT

You can't use format to apply a style attribute to the entire row because a format is evaluated in each cell, so in order to apply a style attribute to an entire row, each cell in that row would need to have the same value.

data test;
	input AFL $ BFL $ CFL $;
	cards;
Y Y Y
Y N Y
N N N
;

proc format;
	value $color
		'Y' = 'green'
		'N' = 'red';
run;

proc report data=test
	style(column)={backgroundcolor=$color.};
run;

To apply a style attribute to an entire using the value of a specific variable, you need to use CALL DEFINE.

/*if using the already created format: $color */
proc report data=test;
	compute CFL;
		call define(_row_,'style',cats('style={backgroundcolor=',put(CFL,$color.),'}'));
	endcomp;
run;

/*using conditional statements */
proc report data=test;
	compute CFL;
		if CFL = 'Y' then
			call define(_row_,'style','style={backgroundcolor=green}');
		else if CFL = 'Y' then
			call define(_row_,'style','style={backgroundcolor=red}');
	endcomp;
run;

The key is the _ROW_ automatic variable that indicates the entire current row.

The REPORT procedure: Formatting a row based on the value of one cell in that row.

mftuchman — Mon, 14 Jun 2021 22:12:40 GMT

Consider the example here:

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/proc/p14xegao6xt0xnn1865r422tpytw.htm

Under the heading, "Using a Format to Assign a Style Attribute Value", you'll see an example where cells are highlighted red if its value is negative.

How would I highlight the entire row as red if the value in the difference column were negative?

Taking this one step further: Can I make the selected row bold, red, and 18 pt, without having to use three separate formats (one for size, one for color, and one for font_weight?)

I can do this with PROC TEMPLATE using CELLSTYLE AS, but I can't find the solution for proc report. Possibly can I use PROC TEMPLATE on the template that PROC REPORT uses?

Re: How to highlight specific text with color at ODS PDF output?

jimbarbour — Mon, 14 Jun 2021 21:09:15 GMT

I ran your code and got the same results. I used two different means to open the pdf, so I don't think it's your pdf reader that is at issue.

The fact that the code works for one ODS destination but not another suggests that one of the following may be true

a) there could be a bug in the SAS software

b) this feature just isn't supported in pdf

I don't really know which it is in this case. seems to know a lot on this subject. Perhaps if she has time she can weigh in.

Jim

Re: Can I assign a style by using a "class" defined in Proc Template?

ballardw — Mon, 14 Jun 2021 20:08:21 GMT

I would start with something like this:

proc template;
source styles.htmlbw;
run;

The LOG will show the definition of the Parent style.

You may have to trace this back a few levels depending on exactly what you want to modify.

That may get you the names of things you want to modify. The syntax is not the same as CSS

If you replace a "class" instead of modifying it you may "orphan" items set in the class that are used elsewhere. You may be looking to override specific class statements and the source will help given a specific parent.

Re: Can I assign a style by using a "class" defined in Proc Template?

Athenkosi — Mon, 14 Jun 2021 19:39:08 GMT

What are you trying to achieve so that I can help you?

Re: Different data sets in multiple excel sheet

Reeza — Mon, 14 Jun 2021 19:01:06 GMT

Quickest way is to wrap your code in ODS EXCEL.

ods excel file="/home/reeza/demo/myfiles.xlsx" options(sheet_interval='proc') style=meadow;

proc freq data=sashelp.class;
table sex*age;
run;

proc freq data=sashelp.cars;
table origin*cylinders;
run;

ods excel close;

wrote:

Hi all,

I have multiple result sets that I saved using OneWayFreqs and I want to export them to one excel file with multiple sheets. Could you help me with that?

Thanks

Re: Different data sets in multiple excel sheet

dustychair — Mon, 14 Jun 2021 18:59:40 GMT

Results are saved in multiple datasets.

Re: Different data sets in multiple excel sheet

Athenkosi — Mon, 14 Jun 2021 18:53:50 GMT

Hello,

Before I give you a solution I just want to make sure that I understand your problem. So your results sets are stored in multiple dataset or in one?

Different data sets in multiple excel sheet

dustychair — Mon, 14 Jun 2021 18:02:15 GMT

Hi all,

I have multiple result sets that I saved using OneWayFreqs and I want to export them to one excel file with multiple sheets. Could you help me with that?

Thanks

Can I assign a style by using a "class" defined in Proc Template?

PhilC — Tue, 15 Jun 2021 13:33:17 GMT

I don't know how to Google this; perhaps I'm getting this wrong. The premise to the idea I'm wondering is this; is it that Proc Templates are suppose to work like CSS. If I'm right, something like this can be done. Or should I just use macros?

<[EDIT]>

What I am wanting to do below is to define a label "TitlePageTitle" for which I associate font attributes. Later, in a (general) procedure, I wish to be able to call that label, using /STYLE=, and have all font attributes applied to the object in the statement. Much like a class attribute in HTML.

proc template;                                                                
  define style Styles.F_Report;                                              
    parent = Styles.HTMLBW;  
    class fonts                                                             
         "Fonts used in the default style" /   
      'TitlePageTitle' = ( "Times New Roman",28pt,Bold Italic)
      'docFont'  = ("Arial Narrow, Helvetica, sans-serif",10pt);
   end;                                                                       
run;

ods PDF
  style=Styles.F_Report
  STARTPAGE=NEVER;

proc ODStext;
p "County Facilities" /STYLE={class=TitlePageTitle};
p "Average Population" /STYLE={class=TitlePageTitle};
p ;
p "[[Date here]]";		                 		/*October 2020*/
p ;
run;

How to highlight specific text with color at ODS PDF output?

abcg — Mon, 14 Jun 2021 17:14:20 GMT

Hi, I wonder how to change the background color of a text string via ODS PDF. It works at ODS RTF, but I don't know why it does not work at ODS PDF. Here is the code:

data aaa;
a=cat('abc',"^{style[backgroundcolor=pink foreground=red]defg}","hi");
output;
a=cat('abc',"^{style[background=pink foreground=red]defg}","hi");
output;
run;

ods escapechar='^';
ods pdf file="test.pdf";
ods rtf file="test.pdf";
proc print data=aaa;
run;
ods _all_ close;

The ODS RTF output returns this, and that is what I want: red text with pink background.

But ODS PDF output returns just red text, background color does not change.

Can anyone show me any issue from my code? Thanks!

Re: proc template - class data cellwidth= and line statements (PDF destination)

xxformat_com — Wed, 09 Jun 2021 15:52:47 GMT

Thank you Cynthia for the time and effort writing such a detailed answer.

Re: proc template - class data cellwidth= and line statements (PDF destination)

Cynthia_sas — Wed, 09 Jun 2021 15:32:52 GMT

Hi:

The behavior that you note is how I would expect all of your posted code to work. Your example #1 works because the style override for the data cells (override in PROC REPORT statement for style(column) option) will ONLY impact the data cells generated by PROC REPORT. The LINE statement will still use the style template settings because the style override on PROC REPORT only impacts the data cells. If you had done this:

or THIS

Then your overrides would have had an impact on the LINE statement output. By default PROC REPORT will use the entire report width to write the LINE text. Note that I used colors above and not cellwidth. If you try to change CELLWIDTH in a PROC REPORT style override, using your #1 syntax you would have observed the same (undesireable) results as with the template in PDF:

This illustrates my point that different destinations have different ways to render the instructions when the PROC REPORT output is bound to the default style template for the destination with the overrides applied. As you can see -- just using PROC REPORT overrides without a style template, PDF behaves differently from HTML.

I find it very problematic to try to "overcontrol" the cellwidth in PDF. You should not need to worry about the cellwidth of LINE statements because PROC REPORT, by default, will make the LINE text span the entire table. Overcontrolling the LINE width doesn't work, as you can see using the simplest example. Adding a style template change into the mix only makes things more complicated.

Your #2 did NOT work in a STYLE template with PDF for 2 reasons: 1) because the LINECONTENT in the style template inherits from the DATA element and 2) not every destination uses style specifications in the same way. So by switching to using a template, and NOT using style overrides in the REPORT syntax, your output was bound by the way inheritance works as shown below:

However, your XXTEST template inherited from PEARL style as the parent; PEARL style inherits from PRINTER style; PRINTER style inherits from DEFAULT; and DEFAULT inherits from the Base.Template.Style. But as you noted, the SAME template is used differently by different destinations: PDF did one thing with the template specification and HTML (and RTF) rendered differently:

HTML and RTF continue allow PROC REPORT to control the spanning of the LINE results, but PDF allowed the template to set the cellwidth and since LINECONTENT inherits from the DATA width that you provded in the new template.

But with your #3 example, your style template change was to the HEADER element --but LINECONTENT does NOT inherit from the HEADER element, it inherits from the DATA element. So #3 example allowed the headers in PROC REPORT to change (which had the side effect of impacting the data cells) and also allowed PROC REPORT to use the default behavior of having the LINE text span the entire table. So, the LINE text was unaffected by the change to the HEADERs in the new template.

The HTML and RTF output for example #3 was the same as the output shown in #2, so it was not included in the screen shot.

My best practice is NOT to tamper with the width of the LINE in PROC REPORT, especially if my desired destination is PDF. I also don't try to "overcontrol" the cellwidths in PDF. PDF destination has some complex algorithms to use fontsize and cellpadding and cellspacing to "draw" the table in PDF format and I find that overcontrolling the cellwidths can sometimes have undesirable results.

Note that in my examples I created different template names and different output names so that I could keep the templates and the output together. I find it easier to test multiple template changes by keeping the names of the templates and the names of the output similar. When I've finished testing, that's when I settle on the final template name and generate the production template code. However, most of time with PROC REPORT, I find it much easier to use style overrides within the PROC REPORT syntax instead of trying to use a STYLE template with PROC REPORT>

This paper https://support.sas.com/resources/papers/proceedings10/033-2010.pdf explains a little more about how inheritance works. It's a bit old because it was written for SAS 9.2, when the template syntax changed for style templates, but much of the information is still good.

Cynthia

proc template - class data cellwidth= and line statements (PDF destination)

xxformat_com — Wed, 09 Jun 2021 12:50:44 GMT

Hi,

Here are three examples. Two work as expected in a PDF destination but not the third one (column width for the line statement) Do you think the output is as expected or a bug?

1/ In this example, everything is as expected.

ods pdf file="&test./reporting/test1.pdf";

proc report data=sashelp.class style(column)=[cellwidth=2cm]; 
    column name--weight; 
    define name--weight   / display; 
    
    compute before;
        line 'Test ';
    endcomp; 
run;

ods pdf close;

2/ But here, one has to use the linecontent class for the output to look ok in a PDF; it is fine in a HTML destination.

proc template;
    define style styles.xxtest;
        parent = styles.pearl;
        class data 
            / outputwidth         = 2cm;
            
        *class linecontent 
            / outputwidth         = 10cm;
            
    end;
run;
                       
*--------------------------------------------------------------------------------;
                                              
ods pdf file="&test./reporting/test2.pdf" style=xxtest;

proc report data=sashelp.class; 
    column name--weight; 
    define name--weight   / display; 
    
    compute before;
        line 'Test ';
    endcomp; 
run;

ods pdf close;

3/ The alternative with class header works properly.

proc template;
    define style styles.xxtest;
        parent = styles.pearl;
   
        class header 
            / outputwidth         = 2cm;
    end;
run;
                       
*--------------------------------------------------------------------------------;
                                              
ods pdf file="&test/reporting/test3.pdf" style=xxtest;

proc report data=sashelp.class; 
    column name--weight; 
    define name--weight   / display; 
    
    compute before;
        line 'Test ';
    endcomp; 
run;

ods pdf close;

Unexpected output

Expected output

Re: Writing ods output to SAS EG results only

js5 — Wed, 02 Jun 2021 06:12:22 GMT

"Outside of the table" would be a row of text, akin to as if I opened an ods destination and ran ods text/proc odstext before running proc print. File print does work to some extent, but it produces a somewhat ugly monospace text in a frame.

Re: Using unicode in a picture format

gabonzo — Tue, 01 Jun 2021 19:28:35 GMT

so, the accepted solution is: you can't use UNICODE in a picture format, correct?

Re: Writing ods output to SAS EG results only

ballardw — Tue, 01 Jun 2021 14:56:23 GMT

Where 'outside of the table'?

Maybe you want a simple FILE PRINT; instead of File ODS.

However you would have to have a PUT statement for the variables when you want them in the output.

Writing ods output to SAS EG results only

js5 — Tue, 01 Jun 2021 09:36:07 GMT

Hello,

I am trying to create a simple program which would run a git status on a folder and do one of the following:

if there is nothing to commit, write a line to the result window stating this
if there are changes, write that and show what needs to be committed

I got most of the way there, but the text gets written to the "path" column. I would like to write it outside of the table:

data _null_;
	length path $255 status $10 staged $5;
	array gitstats path status staged;
	drop files n rc;
	files = git_status("&folder");
	file print ods=(variables=(path status staged));

	if files GT 0 then
		do;
			put "The following changes are present in the directory, please check:";

			do n = 1 to files;
				do over gitstats;
					rc = git_status_get(n, "&folder", vname(gitstats), gitstats);
				end;

				put _ods_;
			end;
		end;
	else put "The folder is clean";
	rc = git_status_free("&folder");
run;

How can I achieve this? Thanks for the help in advance.

Re: Color a table cell using PROC REPORT, using a numeric variable that is different from cell varia

Cynthia_sas — Sat, 29 May 2021 21:06:59 GMT

Hi:

Thanks for posting data. Using your test data and 's suggestion:

As you can see, I get the correct color coding in columns A and B as you intended with your original code.

I explain in the screen shot that the COMPUTE block for A cannot test the value of C because of the PROC REPORT "left-to-right" rule and a timing issue. At the point in time when PROC REPORT is placing A on the report row, it has not yet placed C on the report row. So the COMPUTE block for A cannot test the value of C. However, you can test the value for C in the COMPUTE block for C. At that point in time, the cell for column A has been placed on the report row, so based on the result of the IF in the COMPUTE block a CALL DEFINE in the COMPUTE block for C can "touch" a variable that appears before C in the COLUMN statement. The only trick is that the variable name you want to change with the call define must be quoted, as I show in my code.

Just a few other comments. HEADLINE, HEADSKIP and WIDTH= are ignored by ODS destinations that would use your STYLE. These are LISTING only options and ignored by ODS, so I removed them from my code. I also changed the colors to lightgreen and lightred to make the numbers in the cells more readable.

Hope this helps,

Cynthia

Re: Color a table cell using PROC REPORT, using a numeric variable that is different from cell varia

JeffMeyers — Fri, 28 May 2021 21:51:04 GMT

There's actually no need to change the column order. The variable you put in the compute block is rather arbitrary to what is in the compute block, so the easiest way to do this is to always put the last variable in the COLUMNS statement in the compute statement. This way all the variables are always initialized and you can do whatever style changes are needed (even if they are on a different column than the one in the compute statement and even if there are multiple changes). I think was touching on this in their post.

proc report data=test nowindows headline headskip;
columns a b c;
define a / display ' Level ' width =6;
define b / display ' Home ' width =6;
define c / display ' color' width = 6;
compute c;
if b > 3 then call define('b',"style","style={background = red");
if c > 4 then call define('a',"style","style={background = green");
endcompute;
run;

Re: Color a table cell using PROC REPORT, using a numeric variable that is different from cell varia

ballardw — Fri, 28 May 2021 19:57:41 GMT

Here's one way: change the column order.

proc report data=test nowindows headline headskip;
columns c a b  ;
define a / display ' Level ' width =6;
define b / display ' Home ' width =6;
define c / display ' color' width = 6;
compute b;
if b > 3 then call define(_col_,"style","style={background = red");
endcompute;
compute a;
  if c > 4 then call define(_col_,"style","style={background = green");
endcompute;
run;

Why do this? Proc Report builds the report from left to right. So while construction the column for A, when the column statement is A B C, the value of C (to the Right of A) is not "available" yet.

Same problem with the _cn_ approach, column 3 not available for A (_c1_)

Re: Color a table cell using PROC REPORT, using a numeric variable that is different from cell varia

Reeza — Fri, 28 May 2021 19:50:04 GMT

proc report data=test nowindows headline headskip;
columns a b c ;
define a / display ' Level ' width =6;
define b / display ' Home ' width =6;
define c / display ' color' width = 6;

compute c;
 if c > 4 then call define(a,"style","style={background = green");
 endcompute;

run;

Color a table cell using PROC REPORT, using a numeric variable that is different from cell variable

Dan16 — Fri, 28 May 2021 19:46:14 GMT

Hello,

I would like to color a cell of a table using PROC REPORT, but using a variable that is different from the variable that is in the cell. The problem is reproduceable from this following simple program, and described in greater detail following the simple program.

Sample program:

data test;
input a b c;
cards;
1 2 3
1 2 4
1 2 4
2 4 5
2 4 1
2 4 6
;

proc print;
var a b c;
run;


proc report data=test nowindows headline headskip;
columns a b c ;
define a / display ' Level ' width =6;
define b / display ' Home ' width =6;
define c / display ' color' width = 6;
compute b;
if b > 3 then call define(_col_,"style","style={background = red");
endcompute;
*compute a;
* if c > 4 then call define(_col_,"style","style={background = green");
* endcompute;
compute a;
if _c3_ > 4 then call define(_col_,"style","style={background = green");
endcompute;
run;

In the above program, the first compute statement colors the Home column cells red for those cells where the Home column is greater than 3. This works fine.

However, I would really like to color the cells of Level green, based on the values of the color column.

The second compute statement (that is commented out) does the obvious, which does not work.

The third compute statement uses the _Cn_ approach to identifying the column to create the color values. No error or warnings are produced, but the colored cells do not appear on the table.

The resulting table should have two cells in the Level column colored green. The 4th and 6th cell.

Note that the reason wanting to do this is not clear from the test data, but makes sense in the table I am creating.

Any thoughts from would be greatly appreciated. (Note I could not find anything on this in my searching of the archives).

Sincerely,

Dan

Re: Limiting PDF bookmarks to one per file with IMPORT TO in PROC DOCUMENT

hollandnumerics — Fri, 28 May 2021 19:10:16 GMT

Hi Tom,

The following steps are being done in version 3 of my program and macro for each input text file:

Scan the listing file records to:
- find '0C'x, which marks the 1st record of a new page (IMPORT TO in PROC DOCUMENT ignores new page control characters!) or the last record, and save the records of the beginning and end of page in WORK.PAGES.
- extract the 3rd (line-3) and 4th (line-4) records of the file, which contain the identification number and title of the listing.
- extract the records that begin with a specific string, in this case "sex:", into LINE6 and save in WORK.PAGES, along with the page number, too.
- save each record of the input file in WORK.LISTINGS.
Create a new DOCUMENT in WORK.IMPORT with a new folder labelled as "line-3: line-4".
Copy the records for each page specified in WORK.PAGES from WORK.LISTINGS into individual temporary text files.
Import each individual text files into a new folder in WORK.IMPORT using a separate PROC DOCUMENT step with IMPORT TO. If the page is the first with each LINE6 value, then, before starting PROC DOCUMENT, set ODS PDF BOOKMARKGEN, otherwise set ODS PDF NOBOOKMARKGEN.
REPLAY each individual page in PROC DOCUMENT.

Repeat of these steps for each input text file.

The resulting PDF file looks like this, where text_listings1.txt has 2 pages of "sex: M", but only 1 page is seen in the bookmarks. This means that I now have the correct number of bookmarks, but the top level containing the listing number and title are duplicated. The 3 listing text files shown below are attached to this post.

Using REPLAY to display the whole folder at the same time arranges the bookmarks as pages under the top level of the listing, but does not omit the duplicates. This is because the underlying data in the document has no information about whether a bookmark should be created or not, so a bookmark is created anyway.

I suspect that the solution is probably post-processing of the full bookmark list to remove the duplicates using software other than SAS!

Does that make any sense at all?..................Phil

Re: ODS output tables with variable names as numbers in SAS OnDemand

markgewhite — Fri, 28 May 2021 18:20:02 GMT

Brilliant. This worked. Thanks. Tom was on the right track.

Re: ODS output tables with variable names as numbers in SAS OnDemand

markgewhite — Fri, 28 May 2021 18:19:24 GMT

Thanks. I see where you're coming from, but I'm not creating the variable names. ODS is.

Re: ODS output tables with variable names as numbers in SAS OnDemand

markgewhite — Fri, 28 May 2021 18:16:52 GMT

No. The variables are definitely named 1, 2, etc. Thanks, though.

Re: ODS output tables with variable names as numbers in SAS OnDemand

markgewhite — Fri, 28 May 2021 18:15:43 GMT

I'm afraid that didn't work for me. I tried

proc means data = GLM_DIFF;
	var 'GLM_DIFF'1;
run;

And other such variations.

Re: ODS output tables with variable names as numbers in SAS OnDemand

Reeza — Fri, 28 May 2021 15:53:03 GMT

If you add the following option to your code or autoexec it should help avoid those issues:

options validvarname=v7;

Then it'll generate them as _1, _2 etc.

Re: ODS output tables with variable names as numbers in SAS OnDemand

Tom — Fri, 28 May 2021 15:52:01 GMT

Check the setting of the VALIDVARNAME option.

If you have VALIDVARNAME=V7 then you cannot create a variable with a name like 1.

If you have VALIDVARNAME=ANY then you can. If you have created datasets with names like that then you need to use name literal to reference the variables.

proc means data = GLM_DIFF; 
  var '1'n '2'n '3'n;
run;

Re: ODS output tables with variable names as numbers in SAS OnDemand

sbxkoenk — Fri, 28 May 2021 15:27:23 GMT

It could also be that you are looking at the labels, being 1,2 & 3.

The variable names may still contain an underscore as a prefix.

Koen

Re: ODS output tables with variable names as numbers in SAS OnDemand

sbxkoenk — Fri, 28 May 2021 15:27:50 GMT

Hello,

I think this can be solved by a SAS Name Literal.

Enclose variable name or data set name in quotes immediately followed by little n.

Like in:

data sedanTypes; 
   set cars; 
   by 'Sedan Types'n; 								
   if 'first.Sedan Types'n then type=1;   
run;

See documentation for

Using Name Literals and Avoiding Errors When Using Name Literals.

Koen

Re: Limiting PDF bookmarks to one per file with IMPORT TO in PROC DOCUMENT

Tom — Fri, 28 May 2021 14:35:42 GMT

Can you post the steps you are actually running without the macro to generate them? For example for two files where one of them has at least two pages.

Once you figure out how to change the code the generate the PDF file you can then modify the macro to generate that code.

Re: Limiting PDF bookmarks to one per file with IMPORT TO in PROC DOCUMENT

hollandnumerics — Fri, 28 May 2021 13:58:52 GMT

Version 2 of my macro copies the textfile into separate document folders and then uses REPLAY with WHERE to select each page. This gives me 2-level bookmarks, as I can now show the BY value from each page too, but I'm still seeing duplicate bookmarks with 1 bookmark for every page.

In version 3 I'm going to try splitting up the input file into pages before using IMPORT in PROC DOCUMENT, and then I'm hoping to be able to switch BOOKMARKGEN on or off for each page.

ODS output tables with variable names as numbers in SAS OnDemand

markgewhite — Fri, 28 May 2021 08:37:08 GMT

When using ODS Output I discovered that in SAS OnDemand some tables have variable names as numbers.

Previously in SAS Studio University Edition, those same variables would have had an underscore prefix. eg _1 _2

How am I to refer to these fields in a subsequent statement without getting an error?

Here is a simplified version of what I'm doing. I fit a GLM model and send the LSMeans Difference output to a table. I then try to refer to it but fail.

/* run the model and generate the ODS Output data */
proc glm data=TEST;
	class FactorA FactorB FactorC;
	model Outcome = FactorA FactorB FactorC;
	lsmeans FactorA FactorB FactorC / adjust=tukey pdiff=all alpha=0.05 cl;
	ods output diff = GLM_DIFF;
run;

/* previously offline this would work */
proc means data = GLM_DIFF;
	var _1 _2 _3;
run;

/* but fields are now 1 2 3 */
/* and this fails */
proc means data = GLM_DIFF; 
        var 1 2 3;
run;

I can't figure out what the correct syntax should be. Can someone help?

Thanks

Re: Possibility of creating report of list of SAS files in the folder with specific phrase

Reeza — Thu, 27 May 2021 14:56:13 GMT

Yes it's possible.
See this as a starting point.
https://gist.github.com/statgeek/26cf789d6da0ce941e447022fdccf0f4

Possibility of creating report of list of SAS files in the folder with specific phrase

tmdgus — Thu, 27 May 2021 14:41:26 GMT

Hello,

Is it possible to create a report through SAS that is similar to the spreadsheet below? The goal is search specific phrase within the SAS files in the folder/path and put it as a report.

Example:

File Report

file name	Data step	Proc SQL	Proc Format	Proc Freq
sample1.sas	YES	NO	NO	YES
sample2.sas	YES	YES	YES	NO
sample3.sas	NO	YES	NO	NO

Thank you

Re: Limiting PDF bookmarks to one per file with IMPORT TO in PROC DOCUMENT

hollandnumerics — Wed, 26 May 2021 21:34:30 GMT

Hi Jim,

I think the issue is centred around IMPORT TO, because ODS PROCLABEL and SETLABEL both fail to update the bookmark text.

What would be really nice would be being able to update/remove the bookmarks individually for each page, but I think that may not be possible in this release! 🙄

I'll look forward to your thoughts............Phil

Re: Limiting PDF bookmarks to one per file with IMPORT TO in PROC DOCUMENT

jimbarbour — Wed, 26 May 2021 20:49:19 GMT

Well, if someone like you doesn't know how to do it...

Well, let me think about it and see if anything bubbles up to the surface if I examine the problem but deliberately don't think about it for a while.

Jim

Re: ODS layout Gridded won't publish all tables in ONE PAGE!

Reeza — Wed, 26 May 2021 15:29:21 GMT

It does enable precise control, but you have to specify those 'controls'. I think OP is looking for an option that will scale their output to fit into the specified region which is entirely different than controlling where the output goes. It's about controlling the size of the tables into the region. PROC REPORT has more options for styling directly within the PROC and TABULATE is primarily controlled via templates, so I agree that PROC REPORT is a better solution. However, I don't think Absolute will accomplish what the OP is trying to achieve. They want to put 6 tables on a single page and specify a single option such as FIT TO PAGE that will scale it to the page, similar to ODS EXCEL.

At least that's my understanding of the problem, I'll let the OP clarify what they're trying to accomplish.