ODS and Base Reporting topics

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.

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!

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

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;

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;

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!

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.

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,

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;

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.

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?

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!

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

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

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.

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

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

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

hollandnumerics — Wed, 26 May 2021 14:20:58 GMT

I'm reading existing text files containing new-page characters into PROC DOCUMENT, but to paginate them correctly in ODS PDF I have to replay the individual pages separately.

In the file processing I've extracted the 2 title lines of each file into &line3 and &line4, and I'd really like those macro variables to be used in a bookmark for only the 1st page of each file:

%macro import_text(file=, orient=landscape);
  filename source catalog 'work.import';

  data _null_;
    length line $120;
    retain i prev_n 0;
    infile &file. truncover end = eof;
    input line $char120.;
    file source(import.source);
    if _N_ = 3 then call symput('line3', strip(line));
    else if _N_ = 4 then call symput('line4', strip(line));
    else if substr(line, 1, 1) = '0C'x then do;
      ** If first character is page break, then generate replay **;
      i + 1;
      if i = 1 then do;
        put 'replay textfile (where = (_obs_ lt ' _N_ '));';
      end;
      else do;
        put 'replay textfile (where = (' prev_n ' le _obs_ lt ' _N_ '));';
      end;
      prev_n = _N_;
    end;
    if eof then do;
      put 'replay textfile (where = (' prev_n ' le _obs_));';
    end;
  run;
    
  options orientation = &orient. nodate;

  title " ";

  proc document name = import(write);
    import textfile = &file. to ^;
    obpage \textfile / after;
    setlabel ^ "&line3.: &line4.";
    %include source(import.source) / source2;
  run;
  quit;
%mend import_text;

ods listing close;

ods pdf file = "~/osi_listings/output/osi_listings.pdf"
       pdftoc = 1 contents = yes;

%import_text(file="~/osi_listings/output/lo_hru.L10")
%import_text(file="~/osi_listings/output/lo_s_dth.L10")
%import_text(file="~/osi_listings/output/lo_s_ae_irr.L10")

ods pdf close;

However, when I generate the PDF files I'm getting bookmarks for every page with the full path name of that file:

How can I update my program to use &line3 and &line4 in the bookmarks instead?

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

paris_bloom — Tue, 25 May 2021 14:50:25 GMT

I'm using ODS layout gridded and ODS PDF to publish 6 tables (2 rows and 3 columns) in one page.

However, SAS keeps separating the tables into three pages and publishes two tables on each page only.

I already stated STARTPAGE=NO in the options.

What else am I missing?

I use 6 ODS REGION statements along with the ODS GRIDDED:

Options nodate nonumber /*nocenter*/ orientation=landscape /*papersize=A4*/ leftmargin=0.25in rightmargin=0.001in bottommargin=0.001in; /*avoid date and timestamp on pdf outputs*/
ods pdf file='/test.pdf' NOGTITLE NOGFOOTNOTE STARTPAGE=no;

ods escapechar='~';

ods layout gridded
columns=2 ;

ods region ;
ods text='Text1';
proc tabulate data=sep_dataset s=[just=c ] format=comma12.2;
class servbd /order=data preloadfmt mlf;
class band2 /order=data preloadfmt mlf;
var pri;
table
servbd='Years of Service' ALL='Total'*{style=[font_weight=bold]},
band2='Age Groups'*(N='') ALL='Total'*(n='Total' colpctn='%'*f=pctfmt.)*{style=[font_weight=bold]}
/printmiss misstext='0';

where region='HQ' and status=1;
format servbd $servbd. band2 $Band2_.;
run;

ods region ;
ods text='Text2';
proc tabulate data=sep_dataset s=[just=c] format=comma12.2;
class retfy /order=data preloadfmt mlf;
class branch;
var pri;
table
retfy='Fiscal Years' ALL='Total'*{style=[font_weight=bold]}, pri=''*(N='Number' colpctn='%'*f=pctfmt.*{style=[font_weight=bold]})/*Add column here*/
/printmiss misstext='0';
where region='HQ' and status=1;
format retfy $retfy. ;
run;

ods region ;
ods text='Text3';
proc tabulate data=sep_dataset s=[just=c] format=comma12.2;
class group /order=data preloadfmt mlf;
class level /order=data preloadfmt mlf;
class branch;
var pri;
table
group='Group' ALL='Total'*{style=[font_weight=bold]},
level='Level'*(N='') ALL='Total'*(n='Total' colpctn='%'*f=pctfmt.)*{style=[font_weight=bold]}
/printmiss misstext='0';

where region='HQ' and status=1;
format group $group. level $level.;
run;

ods region ;
ods text='Text4';
proc tabulate data=sep_dataset s=[just=c] format=comma12.2;
class sex/order=data preloadfmt mlf;
class branch;
var age psserv ;
table
sex='Gender' all='Total'*{style=[font_weight=bold]}, age*(mean=''*f=8.1) psserv='Years of Service'*(mean=''*f=8.1)*{style=[just=c ]}

/box='Average' printmiss misstext='0';

where region='HQ' and status=1;
format sex $sex. ;
run;

ods region;
ods text='Text5';
proc tabulate data=sep_dataset s=[just=c] format=comma12.2;
class status /order=data preloadfmt mlf;;

class branch;
var pri ;
table branch='Program' ALL='Total'*{style=[font_weight=bold just=c]},

status='Employment Type'*(n='') ALL='Total'*{style=[font_weight=bold just=c]}*(n='Total' colpctn='%'*f=pctfmt.)
/printmiss misstext='0';

where region='HQ';
format branch $program. status status.;
run;

ods region;
ods text='Text6';
proc tabulate data=sep_dataset s=[just=c] format=comma12.2;
class sex /order=data preloadfmt mlf;
class status;

class branch;
var pri ;
table
sex='Gender' all='Total'*{style=[font_weight=bold just=c]}, status='Employment Type'*(N='') ALL='Total'*{style=[font_weight=bold just=c]}*(N='')
/printmiss misstext='0';

where region='HQ'; *FOR HQ TABLES ONLY;
format sex $sex. status status.;
run;

footnote1 j=left 'footnote';
ods layout end;
ods pdf close;

It is possible to use a format as value for a url= style option?

xxformat_com — Sun, 23 May 2021 18:14:16 GMT

Hi,

It is possible to use a format as value for a url= style option?

Example 1 - Same URL for all the values

ods pdf file='eg1.pdf';

proc report data=class spanrows; 
    column sex--weight;
    define sex / order style(column)=[url = 'https://sas.com'];
    define height--weight  / display;
run;

ods pdf close;

Example 2 - Using a format (doesn't work)

proc format;
    value $ url 'M'='https://sas.com'
                'F'='https://communities.sas.com/';
run;

ods pdf file='eg2.pdf';

proc report data=class spanrows; 
    column sex--weight;
    define sex / order style(column)=[url = $url.];
    define height--weight  / display;

run;

ods pdf close;

Example 3 - Using call define (workaround)

ods pdf file='eg3.pdf';

proc report data=class spanrows; 
    column sex--weight;
    define sex / order;
    define height--weight  / display;
    
    compute sex;
        if sex='M' then call define (_COL_,'url','https://sas.com');
        else call define (_COL_,'url','https://communities.sas.com/');
    endcomp;
run;

ods pdf close;

Regards,

writing source code to a pdf

jtcowder — Mon, 17 May 2021 19:13:45 GMT

I would like to write the source code to a pdf, before displaying the results of the code.

I can use a series of ods pdf text=""; statements, and insert new lines and spacing as needed, but is there a way to put pre formatted text, like with an html <pre> tag so that I do not have to add all the escape characters with newline and nbspace to the source code?

Thank you

Is there any difference between style and style/replace of call define?

xxformat_com — Fri, 14 May 2021 12:19:40 GMT

Hi,

I can understand that first something needs to be defined in order to be replaced

but I cannot figure out a case where there would be a difference in the output of a call define based on the used of style instead of style/replace making style/replace an alias of style.

Do you know any cases where the output would be different?

Regards,

ODS PDF knowing how many rows before page break

jerry898969 — Thu, 13 May 2021 14:59:28 GMT

I have a task to create a pdf report. I have the report generating but I have some table headings on the last line of one page and the data on the next page.

Is there a way to check how many lines are left on a page and if it's not enough add a new page at that point?

Thank you

Why call define works with display variables but not with analysis variables ?

xxformat_com — Tue, 11 May 2021 10:45:27 GMT

Hi,

Why call define works with display variables but not with analysis variables ?

proc report data=sashelp.class;
    column name--height;
    define name   / display;
    define sex    / display;
    define age    / display;
    *define age   / analysis mean;
    define height / analysis mean;
    
    compute age;
       if age=12 then call define(_row_,'style','style=[background=lightpink]');
    endcomp;
    
    rbreak after / summarize;
run;

When age is used as display the row where age=12 will be in lightpink, but by definition, the mean won't be available.

If we change age to analysis mean, the mean will be calculated but the rows won't be in lightpinkt.

Space Between Tables ODS PDF

Doug____ — Tue, 20 Apr 2021 13:28:11 GMT

I used the PARSKIP functionality as shown in the template below, but it did not affect the spacing between the tables in ODS PDF. Was it applied incorrectly?

proc template;
define style styles.BlueK;
	parent=styles.pearl;
	
	class colors /                                                  
		'link2' = cx0000FF
		'link1' = cx800080
		'docbg' = cxFAFBFE
		'contentbg' = cxFAFBFE
		'systitlebg' = cxFAFBFE
		'titlebg' = cxFAFBFE
		'proctitlebg' = cxFFFFFF
		'headerbg' = cxEDF2F9
		'captionbg' = cxFAFBFE
		'captionfg' = cx112277
		'bylinebg' = cxFAFBFE
		'notebg' = cxFAFBFE
		'tablebg' = cxFAFBFE
		'batchbg' = cxFFFFFF
		'systitlefg' = cx112277
		'titlefg' = cx112277
		'proctitlefg' = cx112277
		'bylinefg' = cx112277
		'notefg' = cx112277;
	class Header /
	bordercolor = cxB0B7BB
	backgroundcolor = cxEDF2F9
	color = cx112277;
	class RowHeader /
	bordercolor = cxB0B7BB
	backgroundcolor = cxEDF2F9
	color = cx112277;
	class fonts /
		'TitleFont2' = ("<sans-serif>, <MTsans-serif>, Helvetica, Helv",2, bold)
		'TitleFont' = ("<sans-serif>, <MTsans-serif>, Helvetica, Helv",3,bold)
		'StrongFont' = ("<sans-serif>, <MTsans-serif>, Helvetica, Helv",2, bold)
		'EmphasisFont' = ("<sans-serif>, <MTsans-serif>, Helvetica, Helv",2, italic)
		'FixedFont' = ("<monospace>, Courier",2)
		'BatchFixedFont' = ("SAS Monospace, <monospace>, Courier, monospace",2)
		'FixedHeadingFont' = ("<monospace>, Courier, monospace",2)
		'FixedStrongFont' = ("<monospace>, Courier, monospace",2,bold)
		'FixedEmphasisFont' = ("<monospace>, Courier, monospace",2,italic)
		'headingEmphasisFont' = ("<sans-serif>, <MTsans-serif>, Helvetica, Helv",2,bold italic)
		'headingFont' = ("<sans-serif>, <MTsans-serif>, Helvetica, Helv",2, bold)
		'docFont' = ("<sans-serif>, <MTsans-serif>, Helvetica, Helv",2);
	class Table /
	cellpadding = 2pt;
	style systemfooter from titlesandfooters /
		font_weight=light
		font_size=8pt;
	Style parskip / fontsize=4Pt;
	end;
	run;

Decimal alignment and Page numbering

Dinkepile — Mon, 19 Apr 2021 16:14:15 GMT

Good day

I am struggling to align counts and decimals in this table and also to have page Xof Y.

Please advise.

I have attached the tables observations to be used as example.

Tables data:

COHORT AESOC AEPT sot cat _NAME_ _LABEL_ Not_related Possible Probable Unlikely Total sort _page cnt Not_Related1 Unlikely1 Possible1 Probable1 TOTAL1 _empty
UC Ulcerative Colitis (N=28) COUNT Frequency Count 4 11 0 7 22 1 1 1 4 (14.3) 7 (25.0) 11 (39.3) 0 (0.0) 22 (16.5)
UC 1 Number of Subjects with Any TEAE COUNT Frequency Count 2 10 0 7 19 1 1 2 2 (7.1) 7 (25.0) 10 (35.7) 0 (0.0) 19 (14.3)
UC Cardiac disorders 2 Cardiac Disorders COUNT Frequency Count 0 1 0 1 2 1 1 3 0 (0.0) 1 (3.6) 1 (3.6) 0 (0.0) 2 (1.5)
UC Cardiac disorders Palpitations 2.1 Palpitations COUNT Frequency Count 0 1 0 1 2 1 1 4 0 (0.0) 1 (3.6) 1 (3.6) 0 (0.0) 2 (1.5)
UC Eye disorders 2 Eye Disorders COUNT Frequency Count 1 0 0 0 1 1 1 5 1 (3.6) 0 (0.0) 0 (0.0) 0 (0.0) 1 (0.8)
UC Eye disorders Corneal abrasion 2.1 Corneal Abrasion COUNT Frequency Count 1 0 0 0 1 1 1 6 1 (3.6) 0 (0.0) 0 (0.0) 0 (0.0) 1 (0.8)
UC Gastrointestinal disorders 2 Gastrointestinal Disorders COUNT Frequency Count 2 3 0 2 7 1 1 7 2 (7.1) 2 (7.1) 3 (10.7) 0 (0.0) 7 (5.3)
UC Gastrointestinal disorders Colitis ulcerative 2.1 Colitis Ulcerative COUNT Frequency Count 2 0 0 0 2 1 1 8 2 (7.1) 0 (0.0) 0 (0.0) 0 (0.0) 2 (1.5)
UC Gastrointestinal disorders Frequent bowel movements 2.1 Frequent Bowel Movements COUNT Frequency Count 0 0 0 1 1 1 1 9 0 (0.0) 1 (3.6) 0 (0.0) 0 (0.0) 1 (0.8)
UC Gastrointestinal disorders Nausea 2.1 Nausea COUNT Frequency Count 0 2 0 0 2 1 1 10 0 (0.0) 0 (0.0) 2 (7.1) 0 (0.0) 2 (1.5)
UC Gastrointestinal disorders Tooth infection 2.1 Tooth Infection COUNT Frequency Count 0 1 0 0 1 1 1 11 0 (0.0) 0 (0.0) 1 (3.6) 0 (0.0) 1 (0.8)

style(calldef) unexpected impact on call define ...style/merge... result

xxformat_com — Mon, 12 Apr 2021 08:35:38 GMT

Hi,

I was wondering why the style/merge does not provide the same result when style(calldef) is activated.

Is it a bug or is there another reason behind this result?

proc report data=sashelp.class;* style(calldef)=[foreground=white]; 
    columns name sex age height weight ; 
    define name--weight / display;
    
    compute sex;
        if sex='M' then call define ('name' ,'style','style=[background=lightblue]');
    endcomp;

    compute age;
        if age=12 then call define ('name' ,'style/merge'  ,'style=[foreground=red]');
    endcomp;
run;

Thanks

ps. as requested I had the picture when * style(calldef)=[foreground=white]; is activated

ods tagsets.rtf properties author and title

lucie55 — Fri, 09 Apr 2021 13:31:31 GMT

Hi,

I am using ods tagsets.rtf to output Word document. I want to change the properties of the Word document, the title and the author. I am able to edit the author of the document, but not the title, it still says "9.4 SAS System Output".

Thanks

How to Merge cells horizontally in ODS Excel output.

SASuserlot — Thu, 01 Apr 2021 19:15:07 GMT

I am looking to achieve the ods excel in specific format mentioned in the image( shaded area) . My main aim to merge cells horizontally. I am ok to manipulate the data as per the output requirement. (like displaying the 150/70 across the hr and Vit variables in original dataset in order to merge horizontally). I am not looking for any colors at this point. Please advice your inputs.

Can you please take a look and advice when you have chance.

Thanks everyone.

data dummy;
do round = '1st Round', '2nd Round', '3rd Round';;
do time = "12:30", "13:45","14:15";
do HR = 71 to 76 by 2;
do Vit = 15 to 18 by 1;
do height = 150 to 153 by .5;
do weight = 70 to 77 by 2;
do Orient = 'STANDING', 'SITTING','';
output;
end;
end;
end;
end;
end;
end;
end;
run;

proc sort data = dummy; by round Orient time ;run;

data d1;
set dummy;
by round;
if first.round;
Orient=catx("/",strip(put(HEIGHT,best.)),strip(put(weight,best.)));
Comb = 'Height/Weight';
run;

data d2;
set dummy;
by round Orient time;
if first.time;
run;

data d3;
set d2(drop = height weight) d1(keep =round time Orient Comb);
run;


proc sort data = d3; by round Orient time;run;

data d4;
set d3;
by round Orient time;
if last.Orient;
where not missing(Orient);
if hr ^= . then comb= "Vital";
run;
ods ESCAPECHAR = '^';
ods excel file='C:\temp\New folder\dummy1.xlsx' ;


   PROC REPORT data= d4 nowindows missing spanrows 
					      style(report)=[font=("Times New Roman", 11pt) ]
					      style(header)=[font=("Times New Roman", 11pt, bold) just=center borderbottomwidth=4pt bordercolor=black ]
					      style(column)=[font=("Times New Roman", 11pt)  ] ;
      COLUMN    round comb time  (" "  orient hr vit);
      DEFINE round   /   order  "round" group;* style=[just=l vjust=t cellwidth=5 cm borderbottomwidth=4pt bordercolor=black ];
      DEFINE comb   /  order  "comb" display style=[just=l vjust=t cellwidth=6 cm ];
      DEFINE time    /  "Time" order style=[just=c vjust=t cellwidth=1.5 cm ];
      DEFINE orient   /  order  "orient" display style=[just=c vjust=t cellwidth=4 cm ];
      DEFINE hr   /"hr" display style=[just=c vjust=t cellwidth=2cm ];
      DEFINE vit    /"vit" display style=[just=c vjust=t cellwidth=2cm ];
	  *compute before _page_/ style=[font=("Times New Roman", 11pt, bold ) background=white ];
		*line usubjid $varying80. ;
	  *endcomp;
	  run;
ods excel close;

ODS creating and excel output but table error on opening and no report produced

Elliott — Thu, 01 Apr 2021 15:23:52 GMT

I am having this problem and I think it comes down to some unseen invalid data that causes ODS to give this error.

The report has been running fine for several months, I read in chat data and output into a report for review. The chat data is in a csv file.

Last week I started having an issue where the excel only gives an error message when trying to open.

There are no errors in the log.

The box that pops up when trying to open the excel report .

I have attached a pdf of the box.

When I click ok the excel closes so no report output.

I have tried to strip any unprintable characters out of the field that contains the chat data but still having the same problem.

The tables look fine, I cannot see any issue or strange data.

I need to figure out how to resolve this. Has anyone else have experience with this? I tried to upload the image of the box I get, but the image failed to upload.

Any assistance to resolve this issue will be greatly appreciated.

Thanks,

Elliott

RTF title statement

woshibug — Mon, 29 Mar 2021 13:32:48 GMT

hi，everyone，when I use TITLE statement within Proc report，even I use ‘j=r’ the text will be on the right of then page，but will have a small margin between the edge and the text,the text "Page 1 of 1" in first screenshot has a small space between the true margin. I think it maybe caused by the CellPadding or CellSpacing in 2nd screenshot ，so I try the statement in proc template，but it didn't work. finally,I find it in RTF original code,it has difference with anothers,please see the 3rd screenshot attached.The text in TITLE statement has the attribution “\cellx14350” and text in FOOTNOTE statement is “\cellx14398”,but the main body in table has the attribution “\cellx14400”.I try to modify then to "\cellx14400" manually then the margin in text within TITLE statement and FOOTNOTE statement disappeared,but I don't know where to control it usu code.

ods tagsets.rtf_sample URL

lucie55 — Wed, 24 Mar 2021 16:31:15 GMT

Hi, I am using ods tagsets.rtf_sample to create a Word document, which includes tables, charts, and text sections. I have a paragraph that includes a hyperlink that I want to color and highlight, but not the rest of the paragraph. Currently, URL works on the first part of the text that I don't style, but not on my hyperlink. This is my code:

ods tagsets.rtf_sample text="~S={url='https://www.inspq.qc.ca/'} D'autres résultats peuvent aussi être consultés sur le site internet à l'adresse suivante : ~S={FOREGROUND=#0563c1 textdecoration=underline}www.inspq.qc.ca";

Here is what I get, the visual is correct, but the clickable link is not on the hyperlink, only the front text.

D'autres résultats peuvent aussi être consultés sur le site internet à l'adresse suivante : www.inspq.qc.ca

Thank you for your help

ODS EXCEL - sheet interval options

Reeza — Tue, 23 Mar 2021 19:44:29 GMT

Not sure if I'm missing something obvious here or if there's a bug in ODS EXCEL but this seems to generate an Excel file with two sheets for Asia and then one sheet for each other region.

*sorts your data to use BY logic;
proc sort data=sashelp.cars out=cars;
by origin;
run;



%macro create_report(origin = );
 
 
ods excel options(sheet_interval="NOW" sheet_name = "&origin.");
 
*displays data in Excel sheet;
proc print data=cars;
where origin = "&origin.";
run;

ods excel options(sheet_interval="NONE" sheet_name = "&origin.");

proc sgplot data=cars;
where origin = "&origin.";
scatter x=mpg_city y=mpg_highway / group = type;
run;

%mend;



*removes proc title and by line which are usually printed by default;
ods noptitle;
options nobyline;
options mprint;

*sets file options - notice use of #BYVAL1 in Sheet Name to control sheet names;
ods excel file='/home/fkhurshed/ODS_Example2.xlsx' style=meadow;
 

*create list of origins to run this for;
proc sql;
create table report_list_origins as
select distinct origin
from cars;
quit;

*call macro for each origin;
data _null_;
set report_list_origins;
	str = catt('%create_report(origin=', origin, ');');
	call execute(str);
run;


*closes file;
ods excel close;

Using SAS Academics On Demand, SAS 9.4M6

clickable links to download ods (html, excel, pdf, rtf) files from the results page

supersassy — Mon, 22 Mar 2021 11:08:38 GMT

Hi Community,

I'm writing a code for joining two tables, for which the result is being displayed on the results page.

Now along with the results, I need to write code for clickable links that let me download files in different ODS formats(html, pdf, excel, rtf) on the results page. How should I code this in Base SAS? Thanks in advance!

Putting Free text above a title line without the output

ssitharath0420 — Fri, 19 Mar 2021 21:21:52 GMT

Is there a way to put free texts above your title within your output? I tried it but it gave me an error that it needs to be placed by a group.

Below is my code:

ods excel file="\\fenrir\DEVL\sam_s\Standard Opioid ODS Conversion\Summary_test.xlsx" options(start_at="1,1"
frozen_headers="5"
frozen_rowheaders="3"
/*autofilter="1-5"*/
sheet_name="Summary"
row_repeat="1"
embedded_titles="yes");

proc report split='~' data=Table1_Final out=Table1_Final_1 spanrows style(header)=[fontweight=bold background=lightblue foreground=black]
style(column)={font_face='Calibri' fontsize=11pt};
column Status P4 P3 P2 P1;
define Status / Display "";
define P1 / Analysis "7/1/20 -~ 12/31/2020" style(column)=[cellwidth=15% textalign=center just=decimal];
define P2 / Analysis "4/1/20 -~ 09/30/2020" style(column)=[cellwidth=15% textalign=center just=decimal];
define P3 / Analysis "1/1/20 -~ 06/30/2020" style(column)=[cellwidth=15% textalign=center just=decimal];
define P4 / Analysis "10/1/19 -~ 03/31/2020" style(column)=[cellwidth=15% textalign=center just=decimal];
title1 '# of Members Filling Opioids';
endcomp;
compute before _Page_ / left;
length text0 - text6 $100;
if _BREAK_= ' ' then
do;
text0="OPIOID OVERUTILIZATION";

text1="HEALTH PLAN GROUP: &Clnt_Name";

text2="CLIENT id: &clntid";

text3="Date Generated: &To_Date";

text4="Evaluation Period: &Eval_Date";

text5="Data Sources: PDW";
end;
line text0 $100.;
line text1 $100.;
line text2 $100.;
line text3 $100.;
line text4 $100.;
line text5 $100.;
endcomp;
compute before Status;
line '';
line '';
endcomp;
run;

ods excel close;

I want it to look like the attachment Example.xlsx but when I run my code it looked like \Summary_test.xlsx

Closing Excel Workbook after ODS is utilized to create report

ssitharath0420 — Wed, 17 Mar 2021 22:19:02 GMT

Hello,

I am creating a report with ODS Excel. I used proc report and it generate an excel output. The Excel open but doesn't automatically closed. I have to close out the excel manually. Is there a command to close the excel automatically once the ODS Proc Report is generated?

Style attribute TEXTINDENT produces 2 spaces in RTF regardless the value of the attribute?

DaanDNR — Mon, 08 Mar 2021 12:19:24 GMT

I tried to use the style attribute TEXTINDENT in a proc report to indent some of the report lines. I used ODS ESCAPECHAR='^' and added the style attribute to the value of the character variable '^{Style [TEXTINDENT=8]' || var || '}'. It works as expected in PDF but for RTF the attribute produces 2 spaces regardless the value used. Even TEXTINDENT=0 results in 2 spaces. I've created a simple program to reproduce the issue. A colleague discovered that setting the attribute using the CALL DEFINE statement does work as expected. Is there any reason why RTF doesn't pick up the attribute when added to the value of the variable?

Value of a single row running across Pages in rtf

varma1987 — Tue, 02 Mar 2021 15:59:51 GMT

I had to concatenate all the Concomitant medications of a subject into one row and it resulted in a column with nearly 100 CM's fitted into one row of max length of $1400. when I have to present this information in a listing which has 12 columns and the max width I can assign for each column is not more than 1 Inch, as a result this specific CM column is running across multiple pages disrupting the footnotes and not making any sense on few pages. What can I do to make it look meaningful and not effect the footnote on each page?

ODS PACKAGE with CALL EXECUTE - not "releasing" created zip file

GBL__ — Mon, 22 Feb 2021 15:33:50 GMT

Hello,

I am creating a zip file using ODS PACKAGE and CALL EXECUTE, and it appears that everything is working as expected EXCEPT that when I go to open the zip file, I get the error "The process cannot access the file because it is being used by another process" i.e. it is still being used by SAS. If I copy the "Data Files.zip" to another folder, then I can open it.

I am unsure exactly what the issue could be as there are no errors in SAS. Please see below for the SAS code used to create the zip files:

%MACRO create_zip_files ;

*******************************************************************************************************************************************
*******************************************************************************************************************************************
** PLACES ALL RAW DATA FILES IN THE CLIENTS FOLDER INTO A TEMP SAS DATA SET ;


FILENAME filelist PIPE "dir /a:-d /b %bquote(""&file_path.\&client_name.\"")" ;

DATA dir_contents(KEEP=_file_names) ;
	LENGTH _file_names $ 500. ;
	INFILE filelist TRUNCOVER ;
	INPUT _file_names $500. ;

		/*  ^   matches at the start of the string (unless used at the start of a character class, which then means negation i.e. [^abc]) */
		/*  ()  defines a capture group, groups pieces of a regular expression together and treats them as a single unit called a subpattern */
		/*  \w  matches any word character (any lowercase or uppercase letter, an underscore (_) character, or any digit) */
		/*  +   quantifier that matches preceding item/value one or more times */
		/*  \   is an escape character (\. matches the actual period character) */
		/*  |   specifies the OR condition (the construct x|y matches either x or y) */ 
		/*  $   matches at the end of the string */
		_perl_string = PRXPARSE("/^(\w+_log\.txt)$|^(\w+_results\.html)$|^(\w+\.sas7bdat)$|^(.+\.zip)$|^(\w+\.gpg)$|^(\w+\.ps1)$/") ;

		/* sends the names of the files in the clients directory if they are NOT log files, results files, SAS data sets, */
		/* zip files, or gpg files (i.e. the clients raw data files) to the temporary SAS data set 'dir_contents' */
		IF NOT PRXMATCH(_perl_string, STRIP(_file_names)) THEN OUTPUT dir_contents ;
RUN ;

FILENAME filelist CLEAR ;


*******************************************************************************************************************************************
*******************************************************************************************************************************************
** SENDS ALL RAW DATA FILES TO Data Files.zip ;


ODS PACKAGE(datazip) OPEN NOPF ;

DATA _NULL_ ;
SYSECHO "%sysfunc(PROPCASE(%sysfunc(TRANSLATE(&client_name., ' ', '_')))) - Creating the Data Files Zip File" ;
	SET dir_contents ;
	/* reads in the raw data file names from the temporary SAS data set created from the PRXPARSE/PRXMATCH functions in the preceding step/process */
	CALL EXECUTE('ODS PACKAGE(datazip) ADD FILE="&file_path.\&client_name.\'||STRIP(_file_names)||'";') ;
RUN ;

ODS PACKAGE(datazip) PUBLISH ARCHIVE PROPERTIES(archive_name="Data Files.zip" archive_path="%bquote(&file_path.\&client_name.\)") ;

ODS PACKAGE(datazip) CLOSE ;

PROC DELETE LIBRARY=work DATA=dir_contents (MEMTYPE=data) ;
SYSECHO "%sysfunc(PROPCASE(%sysfunc(TRANSLATE(&client_name., ' ', '_')))) - Creating the Data Files Zip File" ;
RUN ;


*******************************************************************************************************************************************
*******************************************************************************************************************************************
** SENDS RESULTS, LOG, AND ANALYSIS DATA TO CAATs.zip ;


SYSECHO "%sysfunc(PROPCASE(%sysfunc(TRANSLATE(&client_name., ' ', '_')))) - Creating the CAATs Zip File" ;

ODS PACKAGE(caatszip) OPEN NOPF ;

%if %sysfunc(fileexist("&file_path.\&client_name.\&client_name._&medte._results.html")) %then %do ;

	ODS PACKAGE(caatszip) ADD FILE="&file_path.\&client_name.\&client_name._&medte._results.html" ;

%end ;

%else %do ;

	%put ERROR: The results.html file was not included in the CAATS.zip file ;

%end ;

%if %sysfunc(fileexist("&file_path.\&client_name.\_&client_number._&medte._log.txt")) %then %do ;

	ODS PACKAGE(caatszip) ADD FILE="&file_path.\&client_name.\_&client_number._&medte._log.txt" ;

%end ;

%else %do ;

	%put ERROR: The log.txt file was not included in the CAATS.zip file ;

%end ;

%if %sysfunc(fileexist("&file_path.\&client_name.\&client_number._&medte._analysis.sas7bdat")) %then %do ;

	ODS PACKAGE(caatszip) ADD FILE="&file_path.\&client_name.\&client_number._&medte._analysis.sas7bdat" ;

%end ;

%else %do ;

	%put ERROR: The clients analysis data set was not included in the CAATS.zip file ;

%end ;

%if %sysfunc(fileexist("&file_path.\&client_name.\&client_number._&medte._insurance.sas7bdat")) %then %do ;

	ODS PACKAGE(caatszip) ADD FILE="&file_path.\&client_name.\&client_number._&medte._insurance.sas7bdat" ;

%end ;

%else %do ;

	%put WARNING: Insurance data set was not included in the CAATS.zip file, confirm client does not include insurance information ;

%end ;

ODS PACKAGE(caatszip) PUBLISH ARCHIVE PROPERTIES(archive_name="CAATs.zip" archive_path="&file_path.\&client_name.\") ;

ODS PACKAGE(caatszip) CLOSE ;


*******************************************************************************************************************************************
*******************************************************************************************************************************************
** SENDS PCART DATA TO pcart.zip ;


SYSECHO "%sysfunc(PROPCASE(%sysfunc(TRANSLATE(&client_name., ' ', '_')))) - Creating the PCART Zip File" ;

ODS PACKAGE(pcartzip) OPEN NOPF ;

%if %sysfunc(fileexist("&file_path.\&client_name.\&client_number._&medte._pcart.sas7bdat")) %then %do ;

	ODS PACKAGE(pcartzip) ADD FILE="&file_path.\&client_name.\&client_number._&medte._pcart.sas7bdat" ;

%end ;

ODS PACKAGE(pcartzip) PUBLISH ARCHIVE PROPERTIES(archive_name="&client_number._&medte._pcart.zip" archive_path="&file_path.\&client_name.\") ;

ODS PACKAGE(pcartzip) CLOSE ;


*******************************************************************************************************************************************
*******************************************************************************************************************************************
** END OF PROGRAM ;

%MEND create_zip_files ;

The above code includes only the raw data files included in the path, as the other file types go into separate zip files (and those can be opened with no issues).

I am curious if this is all related to the FILENAME PIPE? I updated that part so as to not to try and include any folders in a zip that may be inside the path.

Log for review (sensitive data removed):

31         %create_zip_files ;

NOTE: The infile FILELIST is:
      Unnamed Pipe Access Device,
      PROCESS=dir /a:-d /b "C:\........\SAS_TESTING\........\",
      RECFM=V,LRECL=32767

NOTE: 7 records were read from the infile FILELIST.
      The minimum record length was 9.
      The maximum record length was 56.
NOTE: The data set WORK.DIR_CONTENTS has 2 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.56 seconds
      user cpu time       0.01 seconds
      system cpu time     0.04 seconds
      memory              819.03k
      OS Memory           23908.00k
      Timestamp           02/22/2021 09:22:43 AM
      Step Count                        363  Switch Count  36
      

NOTE: Fileref FILELIST has been deassigned.

NOTE: There were 2 observations read from the data set WORK.DIR_CONTENTS.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      user cpu time       0.01 seconds
      system cpu time     0.00 seconds
      memory              946.00k
      OS Memory           23908.00k
      Timestamp           02/22/2021 09:22:43 AM
      Step Count                        364  Switch Count  21
      

NOTE: CALL EXECUTE generated line.
1         + ODS PACKAGE(datazip) ADD FILE="C:\........\SAS_TESTING\........\Loan Portfolio Details - Dec 2020 updated 17Feb2021.xlsx";
2         + ODS PACKAGE(datazip) ADD FILE="........\SAS_TESTING\........\_goez_DEC2020_converted_file.csv";
NOTE: Writing DATAZIP file: C:\........\SAS_TESTING\........\Data Files.zip

NOTE: Deleting WORK.DIR_CONTENTS (memtype=DATA).
NOTE: PROCEDURE DELETE used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              21.46k
      OS Memory           23908.00k
      Timestamp           02/22/2021 09:22:57 AM
      Step Count                        365  Switch Count  23
      

ERROR: The results.html file was not included in the CAATS.zip file
ERROR: The log.txt file was not included in the CAATS.zip file
WARNING: Insurance data set was not included in the CAATS.zip file, confirm client does not include insurance information
NOTE: Writing CAATSZIP file: C:\........\SAS_TESTING\........\CAATs.zip
NOTE: Writing PCARTZIP file: C:\........\SAS_TESTING\........\goez_DEC2020_pcart.zip

Thank you very much for any assistance, help, or direction!

Full width compute after text, in narrow report

SwissC — Fri, 19 Feb 2021 16:06:08 GMT

Hi,

I have a fairly narrow report, with just 3 short columns. However I want to add a longer piece of text at the end. Below is some partial code.

proc template;
     define style styles.myrtf;
       parent=styles.rtf;
       style Table from output /
         Background=_UNDEF_
         Rules=groups
         Frame=above
         OUTPUTWIDTH=_undef_
         CELLPADDING=0
         CELLSPACING=0 
         bordertopstyle=solid
         bordertopcolor=black;
    end;
  run;
 
 
PROC REPORT data=adsl nowd headline headskip missing split='#';
  COLUMNS subjid arm trtsdt ;  
  
  DEFINE subjid / order order=data  " "  style={just=l  cellwidth=4cm asis=on};
  DEFINE arm /  style={just=l cellwidth=4cm asis=on};
  DEFINE trtsdt /  style={just=l cellwidth=4cm asis=on };
  
    COMPUTE after _page_;  
      LINE j=l "xxx xxxxxxxx xx xxx xxxxxx xxxxxxxxx xxx xxxxxxxxxx, xx xxx 1 xx xxxxx xxx xx xxxxxxxxx xxxxxx. xxxxxxxx xxxx xx xxxxxxxxx xxxxxx xx xx xxxxxxxx xxxx xxx xxxxxxxx xx xxx 1,";
  ENDCOMP;

RUN;

Now the table can be quite long, and we are using the compute after to add footnotes as we have a lot of them. Hence I would like to be able to span these the full of the page. I know I could set outputwidth=100% in the template, but this would also stretch the 3 columns over the full width of the page. Is there any way around this?

n.b. I know I am missing the ODS RTF statements, but these are in company macros so I removed them for clarity.

PROC FREQ output to ODS Powerpoint: multi-slide output needed

lchristensen — Wed, 17 Feb 2021 20:36:08 GMT

I have code that generates a lot of performance data combining SMF/RMF (mainframe performance) with information on my firm's disk subsystems. What is attached is a small part of what will exist. I'm looking to produce a single-run that creates a PowerPoint that is going to be hundreds of slides. The code includes a lot of PROC FREQs that generate multiple pages of print, and also a lot of SGPLOT output.

I'm concerned that with PROC FREQ and ODS POWERPOINT I can't seem to set a number of lines of data for each PowerPoint slide, it seems to be trying to write more lines than can fit on a single slide.and I see a lot of text well outside the borders of the slide. With one example PROC FREQ in this code, doing a TABLE MARSSN * MARSCU, there might be as many as 10 subsystems (identified by serial number in MARSSN) and within each theoretically as many as 256 control units (MARSCU)) and that frequency table could be dozens of pages.

ODS seems to be very powerful but complicated. I really don't want to have to write a thousand lines of code for ODS templates for an eight-line proc.

Can anyone suggest a way of limiting the number of lines of PROC FREQ output per slide? I thought maybe an OPTIONS PAGESIZE=10 or something at the start might work but I'm not sure it does. My test data isn't sufficient to generate a multi-page output from these procs.

A minor issue is coming up. I have used "ODS NOPROCTITLE" but that doesn't seem to stop the slides from having a PROC FREQ generated "The FREQ Procedure" title on the slides.

ALIGN VALUES IN PROC REPORT OUTPUT

Dinkepile — Tue, 16 Feb 2021 12:55:33 GMT

Good day

I am trying to align my values of the circled column on the picture below, please advise. I have attached an example of an output with proper aligned values. I have also attached a code to be used as an example to use:

data class;

set sashelp.class;

height = height+.8;

weight = weight+.9;

if name in('Alfred' 'Carol' 'James')

then do;

height = 88.88;

weight =77.77;

end;

if age=14 then count_percent=strip(put(height,6.))||'(12.13)';
else if age=13 then count_percent=strip(put(height,6.))||'(89.3)';
else count_percent=strip(put(height,6.))||'(100.00)';

run;

ods listing close;

title; footnote;

ods tagsets.RTF file='C:\Users\just_d.rtf';

proc report data=class nowd headline headskip split='~' missing style ( report )=[frame=hsides outputwidth = 10in background = white
font_face='Courier New' font_size=3]
style(column)={just=CENTER font_face='Courier New' background=white foreground=black
font_size=3 cellwidth=.6in}
style(header)={just=center font_face='Courier New' cellheight=.8in font_size=3
foreground=black cellwidth=.6in
background=white font_style=roman vjust=t} ;

column name age height weight weight=wt2 count_percent;

define height / 'ht Just=c'

style(column)={just=c};

define weight / 'wt just=d'

style(column)={just=d};

define wt2 / 'wt just=d/chg margin'

style(column)={just=d width=1.0in rightmargin=.25in};

define count_percent / 'wt just=d/chg margin'

style(column)={just=d width=1.0in rightmargin=.25in};

run;

ods _all_ close;

on the final report or output i wish the values to align like the example below:

Producing a report in a specific arrangement

nduksy — Fri, 12 Feb 2021 18:33:13 GMT

Hello All,

I have the dataset below

...and I have been asked to present/generate a table like the one below.

I have played around with proc report but no luck yet. I don't know how to manipulate the data so that the numbers are placed in the right column/rows within my report.

I will appreciate some help.

Thanks

How to add dynamically weekday column counts in the report as the week progress?

Stalk — Wed, 10 Feb 2021 17:11:07 GMT

I am working on a report to automatically send daily report to the users with a table in the body of the email. One of the requirement is to have a column for total count, prior week counts and this weeks counts( break down by day).. Not sure how to add the columns as the week progress with their respective header names.

started to get last week dates but not sure how to add the columns in the proc report.. some times there might not be any data for that day but still need to show for the 3 products..

%let fdlw=%sysfunc(intnx(week,%sysfunc(today()),-1,b),date9.);

%put first_day=&fdlw ;

%let ldlw=%sysfunc(intnx(week,%sysfunc(today()),0,b),date9.);

%put last_day=&ldlw ;

PROC REPORT DATA=daily_Counts nowd HEADLINE HEADSKIP SPLIT='*'

style (report) = {background = white

font_face = "Verdana" font_size = 7pt just=left }

style (column) = {background = white CELLHEIGHT = 2.5%

font_face = "Verdana" font_size = 7pt just=left}

style (header) = {foreground = cx5e2750 font_face="Verdana"

font_size = 8pt just=left

background = white} ;

column product Description Total_counts Prior_week Day1 Day2

;

define Product / display width= 15 "Product";

define Description / display width= 30 " Description ";

define Total_counts / display width= 30 " ";

define prior_week / display width= 30 "”;

define Day1 / display width= 30 " ";

run;

Product	Description	Total Products in the system	Total Products in the system Received Prior week( Sunday to Saturday)	Total Products in the system Received Sunday	Total Products in the system Received Monday
1235	Item1	3173	1070	73	34
5432	Item4	340	96	19	2
6574	Item5	5	2	-	3

create SAS help pages and examples

LauraRK — Wed, 03 Feb 2021 17:29:08 GMT

Is there SAS code that creates attractive documents like the SAS online help, https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=statug&docsetTarget=statug_anova_examples01.htm&locale=en

or is the SAS online help created outside of SAS? I like how in the online help there is little code snippets and the ability to copy those snippets and that it looks attractive and keeps the color coding.

I have a little macro,

%macro echocode(sasfile=sasprogram);
Proc document name=temp(write) ;
Import textfile="&projdir\&sasfile..sas" to ^;
Run;
Replay;
Run;
Quit;
%include "&projdir.\&sasfile..sas";
%mend;

but the code is not nicely formated. I tried to further develop my own system to divide up my code but I still had some issues, mainly I can't figure out ways to keep the tabs. I really want an Rmarkdown type solution. I could connect Rstudio to SAS, but I would rather program in SAS studio or SAS EG when I write SAS code. I learned Statrep, but I feel there must be an easier way to do this that does not involve using latex, I like latex, but in my classes that use R, I literally can give students an Rmarkdown document on the first day and have them knit it and make a nice document with code and output and I keep thinking there must be a way I could set this up in SAS as well that is not so complicated? Am I missing something obvious?

ODS PDF: Suppress header if data flows to multiple pages

SuryaKiran — Thu, 28 Jan 2021 19:44:58 GMT

Hello Community,

Trying to find a way to suppress headers if the data flows to multiple pages in the same PROC REPORT.

data class;
set sashelp.class sashelp.class sashelp.class;
run;

ods pdf file="/user/Test_for_Header.pdf" ;
proc report data=class;
run;
ods pdf close;

I don't want the header on page 2.

Style element associated with FOOTNOTES

data_null__ — Wed, 27 Jan 2021 22:30:52 GMT

I am trying to create a custom style template based on the RTF style. I want FOOTNOTES to have different default attributes from TITLES. As far as I can tell footnotes use these style attributes 'TitleFont' = ("<serif>, Times Roman",13pt,bold italic)

Is there a way to create a 'FootFont' = ("<serif>, Times Roman",13pt,bold italic) that will be used by footnotes by default.

define style Styles.Rtf;
   parent = styles.printer;
   style fonts /
      'TitleFont2' = ("<serif>, Times Roman",12pt,bold italic)
      'TitleFont' = ("<serif>, Times Roman",13pt,bold italic)
      'StrongFont' = ("<serif>, Times Roman",10pt,bold)
      'EmphasisFont' = ("<serif>, Times Roman",10pt,italic)
      'FixedEmphasisFont' = ("<monospace>, Courier",9pt,italic)
      'FixedStrongFont' = ("<monospace>, Courier",9pt,bold)
      'FixedHeadingFont' = ("<monospace>, Courier",9pt,bold)
      'BatchFixedFont' = ("SAS Monospace, <monospace>, Courier",6.7pt)
      'FixedFont' = ("<monospace>, Courier",9pt)
      'headingEmphasisFont' = ("<serif>, Times Roman",11pt,bold italic)
      'headingFont' = ("<serif>, Times Roman",11pt,bold)
      'docFont' = ("<serif>, Times Roman",10pt);

EDIT: I think I figured it.

      class SystemFooter /
         font = Fonts('FootnoteFont');

PROC ODSTABLE, format not being applied to column

PhilC — Wed, 27 Jan 2021 19:45:46 GMT

Please check me on this code. What am I missing? Is this a bug?

Using SAShelp.Cars, I'm creating a character format, $MAKE, which maps the first three (3) characters of the column Make to the full text. Then I'm attempting to create a table using ODSTABLE giving the 3 character data to the procedure and I'm wanting the procedure to transform that data by the format. This is suppose to work like the example here: SAS Help Center: Defining Variables with the COLUMN Statement -- I think. (??)

options nofmterr nocenter;

data cntlin (keep=FMTNAME end start label type );
  set SAShelp.Cars (obs=50);
    by Make ;
  if first.Make then do;
    FMTNAME="Make";
    type="C";
    length start end $3;
      start=substr(Make,1,3);
      format start $Make.;
    end=start;
    label=Make;
    output;
  end;
proc format cntlin=&sysLast;
run;

proc odstable data=cntlin(Keep=start ) PAGEBREAK=No;
  column   start ;
  define start;
    format=$make.;
  end;
run;

I get this output like this:

start Acu Aud BMW Bui

I expected this:

start Acura Audi BMW Buick

ODS EXCEL using Calibri on Unix/Linux Server

xueke47 — Tue, 26 Jan 2021 14:26:01 GMT

Hi all,

My company needs to generate some Excel files in the Calibri font. I wrote the code using ODS EXCEL and PROC REPORT that produces the desired output in both SAS Base (9.4 TS1M5) and SAS EG (7.15 HF3) on my company's computers, which are on Windows Server 2012R2. However, when my colleague takes the same code to run in SAS EBI on the CMS server (which is Unix/Linux), the font never comes in Calibri, but other formats of the Excel files are as coded. I did some research and have a clue about the reason. Could someone confirm/disconfirm? Thanks!

My guess is that Calibri is not installed/registered on the CMS server, so the SAS EBI there cannot recognize the "font_face=Calibri" option. I get this idea from the post here, but one difference is that our log doesn't have "WARNING: Font specification 'Calibri' failed. Using default font family instead." in it. In addition, if I run the following code in SAS EBI on the CMS server, and don't see Calibri in the log, does this confirm my guess?

proc registry startat="\CORE\PRINTING\FREETYPE\FONTS" list levels=1;

run;

Thanks!

Dynmic data representation - updating Excel Pivot tables?

Stalk — Mon, 25 Jan 2021 23:02:12 GMT

I am looking for a best way to process my data and export to excel so the users will have flexibility to filter and get the summary counts.

So far what I have done in my SAS program is: I applied all the filters and the business rules and created a line list. Using Excel engine I wrote this line list to a pre-existing Excel template where I created a pivot table with all the row and column counts and grand total along with standard bar graphs. When I run this every afternoon the line list is overwriting the existing Excel table so my pivot table is not refreshing with new data. Running SAS on local machine (windows environment). Open to any suggestions to give flexibility to my users to filter by start date, end date, dept. etc and get the overall counts.

Thank you

TAGSETS.RTF, PROC ODSTABLE, Margins, Page Headers, Tables, and Spacing.

PhilC — Mon, 25 Jan 2021 16:13:56 GMT

I have four goals to learn about ODS TAGSETS.RTF and or PROC TEMPLATE. Could you, kindly, help me learn to do some of these goals using this mock document.

Change all page margins to 1". ✓
Remove date, time and page number from the top of the page. ✓
Remove grey border from table, or set color to white. ✓
Implement a half line space between title lines. IOW implement 0.5 line spacing, or adding spacing before and after paragraph. ✓

ods tagsets.rtf 
  style= daisy
  options (
    VSPACE='OFF'
  )
  ;

option nocenter;
title;

/*Because Proc Template is hard:*/
%let normal=font_size=11pt;
%let answer=[fontweight=bold liststyletype="none"];
%let table_answer=[fontweight=bold &normal];
%let systemtitle=[fontweight=bold font_size=12pt];
%let sub_title=[fontweight=bold &normal];
%let mid_title=[fontweight=bold font_size=14pt just=c];
%let normal=[font_size=11pt];
%let Small_line=[font_size=5pt];

proc odstext;
  p "Neque Porro Quisquam est qui Dolorem Ipsum " /style=&systemtitle;
  p " "/style=&Small_line;
  p "Quia Dolor sit Amet, Consectetur, Adipisci Velit"/style=&sub_title;
  p " "/style=&Small_line;
  p "Objectives:"/style=&sub_title;
  p;
  p '10A.  Mauris iaculis libero a purus eleifend viverra.'/style=&normal;
  p;
run;

%let question_10A_make=0;
  proc sql noprint ;
    select distinct Make, count(*)
      into :null, :question_10A_make
      from sashelp.CARS
        where Make='Acura' 
	   ;
  run;

proc odstext;
  list;
    item "%CMPRES(&question_10A_make)"/style=&answer ;
  end;
  p;
  p "Donec Sagittis Neque Quis Starting 12-2017" /style=&mid_title ;
  p;
  p '15B.  Lorem ipsum dolor sit amet, consectetur adipiscing elit.'/style=&normal;
  p;
run;

PROC FREQ DATA=sashelp.cars (obs=50);
  TABLES Make / NOPRINT out=make ;
proc odstable data=make PAGEBREAK=NO;
  column blank Make count;
  define Make ;
     format=$ENRLST.;
     PRINT_HEADERS=OFF;
  end;
  define count;
    style=&table_answer;
    format=3.0;
    PRINT_HEADERS=OFF;
  end;
  define column  blank;
     style={cellwidth=0.6in};
     PRINT_HEADERS=OFF;
     compute as " ";
  end;
run;

proc odstext;
  p;p;
  p "To Do:"  /style=&systemtitle;
  p;
  list;
    item 'Change all page margins to 1"';
    item "Revove date, time and page number from the top of the page.";
    item "Remove grey border from table, or set color to white.";
    item "Implment a half line space between title lines.";
  end;
run;
ods tagsets.rtf close;

Placing images on the same .eps or .wmf file

A_M_ — Tue, 19 Jan 2021 12:47:59 GMT

Hello,

I am working on a figure where I put 6 images into a single final figure. I am able to manage and do it on a pdf output file (using ods pdf and ods layout). I can't seem to be able to generate the same final figure (combining many figures into one) in .eps or .wmf directly from SAS though. Any experience/ideas with that?

Thanks!

Quarters- months grouping in SasJobExecution

gopalxor — Mon, 18 Jan 2021 14:46:48 GMT

Hi Team,

I am trying to export the report from Proc report , with the attached input from dataset and optput mentioned in the dataset.

I tried , but for me all the months are coming in each quarter

proc report data= xxxx;
column 'Dept'n 'loc'n 'QUARTER'n,('MonthYear'n,('count'n 'Amount'n));
define 'Dept'n / group;
define 'loc'n / group;
define 'QUARTER'n / across;
define 'MonthYear'n / across;

run;

Reduce vertical space between two titles in Powerpoint slide

ghosh — Sun, 10 Jan 2021 05:08:33 GMT

proc template; 
	define style styles.mystyle; 
	parent=styles.powerpointlight;
	class SystemTitle /    fontsize=24pt textalign=left  ;
	class SystemTitle2 /    fontsize=12pt textalign=right ;
	end; 
run;

I would like to reduce the vertical space between the two titles in my Powerpoint slide which I am generating with ODS Gridded layout using this modified style template.

I have tried various options that EG shows while editing the code, none of which works. I would appreciate any suggestions

Thanks.

Trying to create groupings from computed variables

vincent_s_jones — Fri, 08 Jan 2021 20:29:11 GMT

I've searched through the knowledge base, web and message boards and I could not find a similar example, although I know it exist somewhere.

So, I have a small data set

data salaries;
   input team $  type $ salary bonus;
   datalines;
Ben OFF 150000 5000
Tom OFF 200000 2000
Jerry OFF 140000 1500
Al DEF 80000 1000
Bill DEF 90000 2000
;
	run;
quit;

and I'm trying to group and calculate the salary + bonus (salary_plus).

proc report data=salaries;
	column type team salary salary_plus bonus;
	define type / group 'Type';
	define team / display;
	define bonus / analysis format=dollar32.2 sum;
	define salary / analysis format=dollar32.2 sum;
	define salary_plus / computed noprint ;
	
	compute salary_plus;
		salary_plus= bonus+salary;
	endcomp;

	break after type / summarize
		style=[font_weight=bold background=lightgray];

	compute after type ;
			line 'Total ' salary_plus dollar7.2;
	endcomp;


run;
quit;

I'm getting the correct report structure, but at the very end for the Total, I'm getting a null value. I'd like for it to say

$200,000 for DEF and $498,500 for OFF

Percent8. format for specific report rows

covershaker45 — Thu, 31 Dec 2020 21:24:18 GMT

Hello, I need assistance with proc report. I'm trying to create a spreadsheet that contains numeric data with a format of 8. for all rows except where variable category = 'Ratio', then I would like to use percent8.

My data looks like:

Name	Title	Dept	Category	EOD1201	EOD1202	EOD1203	EOD1204	EOD1205	Change
Doe, John	CEO	DEP	AMR	5	6	10	12	15	3
Doe, Jan	VP	RSK	Ratio	.23	.25	.75	.88	.25	0
Smith, Bob	SVP	SMB	MDR	7	8	9	9	9	0
Smith, Mike	Associate	CHK	SUB	6	6	8	8	7	1

I want it to look like:

Name	Title	Dept	Category	EOD1201	EOD1202	EOD1203	EOD1204	EOD1205	Change
Doe, John	CEO	DEP	AMR	5	6	10	12	15	3
Doe, Jan	VP	RSK	Ratio	23%	25%	75%	88%	25%	0
Smith, Bob	SVP	SMB	MDR	7	8	9	9	9	0
Smith, Mike	Associate	CHK	SUB	6	6	8	8	7	1

Each day a new column (Variable) is added to reflect the previous days information. Few questions:

1. How can I use proc report to update the format?

2. How should the column statement be coded to account for new columns added?

3. Do I use the compute to update the format?

Current proc report looks like:

proc report data=New_Report nowd

     style(header)={font_face="Arial" font_size=8pt color=white backgroundcolor=CX000000 tagattr='wraptext:no' width=100%}
     style(report)={font_face="Arial" font_size=8pt tagattr='wraptext:no' width=100%}
     style(lines)={just=left font_face=Arial font_size=8pt tagattr='wraptext:no' width=100%}
     style(column)={font_face="Arial" font_size=8pt tagattr='wraptext:no' width=100%};

run;

Any assistance would really be appreciated.

Does SAS allow different column widths and alignments in multiple tabs of the spreadsheet?

ph8252 — Mon, 28 Dec 2020 18:00:07 GMT

Hi SAS experts,

I had go over many posts that discuss how to adjust column widths for excel outputs but I couldn't find anything that fits my needs (or I just miss it, please share if you find one!)

#1) I am generating an excel output with multiple tabs and I want some of the columns to have their width autofit to the length of the text in the observations. For example, in column "location_type" I have only "office" in one tab and "Skilled nursing facility" in another tab. I want to adjust the widths to be only as wide as the text. I have tried absolute_column_width (see code) and this standardizes the column widths across tabs. Is there a way to do it?

#2) I currently have all my columns left-aligned (as default) but I want "location" and "Inv" to be center-aligned. Is there a way to specify alignments to some of the columns?

Please advise! Thank you for your help.

ODS EXCEL FILE="C:\Users\TestOB_&run..xlsx"
OPTIONS (Orientation = 'landscape' 
sheet_name = "OB List"
sheet_interval = 'Proc'
embedded_titles='yes' 
AUTOFILTER ="All"
FLOW="TABLES"
ABSOLUTE_COLUMN_WIDTH="5,11,8,14,12,58,59,5,25.5,15,12,12,12,12"
ROW_HEIGHTS="39, 18"); 
ods listing close;
Proc report data =extract2 nowd style(header)=[Backgroundcolor = white color=Black] style(report)=[just=center];
column OBs closure priority list_number date_created location location_type district Inv Ncv var1 var2 var3 var4;
compute closure;
 if closure = "Yes" then
     call define("closure", "style", "style=[backgroundcolor=orange]"); /*Highlight Closure = "Yes"*/
endcomp;
define OBs / computed;
compute obs;
 dsobs + 1;
 obs = dsobs;
 endcomp;
run;

%macro ByNcv;
*add loop to the ODS Code;
%do I=1 %to &extn ;
ODS EXCEL
OPTIONS (Orientation = 'landscape' 
sheet_name = "&&S&I"
sheet_interval = 'Proc'
embedded_titles='yes' 
AUTOFILTER ="All"
FLOW="TABLES"
ABSOLUTE_COLUMN_WIDTH="5,11,8,14,12,58,59,5,25.5,15,12,12,12,12"); 
ods listing close;
Proc report data =extract2 nowd style(header)=[Backgroundcolor = white color=Black] style(report)=[just=center];
	where Ncv = "&&S&I";
column OBs closure priority list_number date_created location location_type district Inv Ncv var1 var2 var3 var4;
define OBs / computed;
compute obs;
 dsobs + 1;
 obs = dsobs;
 endcomp;
compute closure;
 if closure = "Yes" then
     call define("closure", "style", "style=[backgroundcolor=orange]"); /*Highlight Closure = "Yes"*/
endcomp;
run;
%END;
%MEND;

%ByNcv;

ODS EXCEL CLOSE;

Proc Report TITLE: Display all values chosen from a multi-value prompt on the title line

CLE — Wed, 23 Dec 2020 17:38:55 GMT

I have a ODS proc report that uses values from a multi-value prompt. I need to display all values selected for each prompt in the Title statments. Using the method below, it only produces the first selction and ignores the rest for the title statments. Any advise on how to get it to display all values selected for each prompt.

Example: Prompt2: User selects A, B, andC from list. On Title statement it shows Prompt2: A instead of Prompt2: A B C.

Title font=arial height=16pt color=bib justify=center "Prompt1: &Prompt1" ;

Title2 font=arial height=12pt color=bib justify=center "Prompt2: &Prompt2" ;

Title3 font=arial height=12pt color=bib justify=center "Prompt3: &Prompt3" ;

Title4 font=arial height=12pt color=bib justify=center "Prompt4: &Prompt4" ;

Title5 font=arial height=12pt color=bib justify=center "Prompt5: &Prompt5" ;

Title6 font=arial height=12pt color=bib justify=center "Prompt6: &Prompt6" ;

Title7 font=arial height=12pt color=bib justify=center "Prompt7: &Prompt7" ;

remove numbering of filename

mmea — Fri, 18 Dec 2020 07:45:11 GMT

Hi I have this code for a SGPLOT

%let numdate = %sysfunc(today(), yymmddn8.);
Ods listing gpath="F:\Folder";
ods graphics / imagename="Test_&numdate." imagefmt=png;
options orientation=landscape;
ods noproctitle;

PROC SGPLOT DATA = new PCTLEVEL=GROUP NOBORDER NOWALL NOCYCLEATTRS;
VBAR KMA / GROUP = myint stat= percent NOOUTLINE;       
TITLE 'Svartider i sundhedssporet per region for den foregående uge';
keylegend / title="";
XAXIS DISPLAY=(NOLABEL);
YAXIS DISPLAY=(NOLABEL);
FORMAT myint intervalfmt.;
styleattrs DATACOLORS=(LightBlue STEEL Mistyrose FireBrick);
RUN; 
ODS LISTING CLOSE;

When it is saved in the folder it will always be: test_(todaysdate)1

it will always put a number behind the date, i just want the date without numbering.

Is that possible?

Another question when it comes to that:

I have this code for sending out e-mails

filename outbox email
      from=("test@test.dk")
	  to=("test@test.dk")
	  type='text/html'
	  subject='testt'
	  attach=("F:\Folder\Test_202012112.png" ct='png')
	  attach=("F:\Folder\\test_202012112.xlsx" content_type="excel")
ods html body=outbox rs=none style=Htmlblue;

run;
ods html close;

Is there a function in SAS where it can by it self find the newest file ? instead of writing files manually in the email code

ODS PDF - Text disappears when the document grows to two pages

ziporapr — Tue, 15 Dec 2020 08:20:15 GMT

I need to process a dynamic document. When the document is short it works great. When it is too long the text disappears from the PDF. Attached is a sample SAS

code. If the following lines are starred:

% hachpala (8);

% hachpala (9);

The file comes out fine, otherwise the page goes wrong. Why? What can be done to make it work well?

%let koteret1=^S={font_size=10pt font_face=Arial FONT_WEIGHT=Bold just=right};
%let text1=^S={font_size=10pt font_face=Arial just=right};
%let kav_mafrid=^S={font_size=8pt color=bgr }____________________________________________________________________________________________________________________________;
%let path=C:\Users\xxx\Desktop;

ods _all_ close;
options 
orientation = portrait     spool     lrecl=max     urlencoding=utf8      dev=actximg      locale=HE_IL   papersize= A4 
nodate  nonumber topmargin= 0cm  bottommargin = 0cm leftmargin = 1cm rightmargin = 1cm       printerpath= pdf;    
ods noproctitle;
title;
footnote;
ods escapechar = '^';
ods pdf file="&path.\test.pdf"    /*uniform*/   /* notoc*/ startpage=yes /*gtitle gfootnote*/ ;
title1 j=center "סמל ארגון";
title2 j=left "כותרת שמאל" 
           j=center "^{style [font_size=12pt font_face=Arial FONT_WEIGHT=Bold color=bgr ] שם ארגון }"
          j=right "כותרת ימין" ;

footnote1 j=center "^{lastpage} דף ^{thispage} מתוך";
%macro hachpala(mis_hadpasa);
ods layout start  columns=1;
     ods region width=100% style={just=right};
         ods pdf text=" &kav_mafrid ";     
                ods pdf text="^{ newline 1}";
           ods pdf text="&koteret1 כותרת &mis_hadpasa";     
           ods pdf text=" &kav_mafrid ";    
ods layout end;

     ods pdf text="^{ newline 1}";

%macro ods_pdf_text(N);
     ods pdf text="&text1 עמודה &N";
     ods pdf text="&text1 עמודה &N";
     ods pdf text="&text1 עמודה &N";
%mend;

ods layout start  columns=4;
     ods region width=25% style={just=right};
           %ods_pdf_text(4);
     ods region width=25% style={just=right};
           %ods_pdf_text(3);
     ods region width=25% style={just=right};
           %ods_pdf_text(2);
     ods region  width=25% style={just=right};
           %ods_pdf_text(1);
ods layout end;
%mend;
%hachpala(1);
%hachpala(2);
%hachpala(3);
%hachpala(4);
%hachpala(5);
%hachpala(6);
%hachpala(7);
%hachpala(8);
%hachpala(9);

ods pdf close;
ods _all_ close;

Proc report define statement order options output problems

Maplefin — Mon, 14 Dec 2020 03:56:47 GMT

Hi, I have some problems about Proc report outputs. I want to use ORDER option to suppress some repetitive values, but got a unexpected output. Here's my codes:

data x1;
informat subject armcd period $20.;
input subject armcd period;
cards;
C007 RT 2
C007 RT 2
C010 RT 1
C010 RT 1
C010 RT 1
C010 RT 1
C010 RT 2
C010 RT 2
C010 RT 2
C010 RT 2
C010 RT 2
;
proc sort;
by subject armcd period;
run;

proc report data=x1;
column subject armcd period;
define subject/order order=data;
define armcd/order order=data;
define period/order order=data;
run;

I thought the output would be:

I was surprised I got:

So, why the output would be like this?Why the value "1" is showing below the value "2"?Could someone explain to me? Any help will be greatly appreciated.

Working with word field codes

js5 — Wed, 09 Dec 2020 13:51:35 GMT

Dear community,

Is it possible to directly put word field codes in titles, footnotes, proc report posttext and line statements, etc? According to https://www.sas.com/content/dam/SAS/support/en/sas-global-forum-proceedings/2019/3235-2019.pdf, this is possible with proc template, but this would just modify existing template elements.

What I am trying to achieve is to hand over table numbering to Word for easy list-of-tables creation. According to google, the field code for table number is {SEQ Table \* ARABIC}. Unfortunately, if I use it as-is, the literal is inserted to document, and prepending it with an ods escapechar (~{SEQ Table \* ARABIC}) results in nothing being inserted. Thank you.

Conditional Formatting of Columns in ODS Excel Report

rajesh1980 — Wed, 09 Dec 2020 08:05:34 GMT

Hi,

I am creating a ODS Excel PROC REPORT where i have a attribute column which is displayed across along with a metric column to display the metric. The attribute column has revenue figures as well as other KPIs. The revenue figures i want them to be formatted with a $ symbol and the other KPIs as normal.

This is the code i have , the CHARGE_TEXT field is the KPI column and CHARGE is metric column.

Im using a COMPUTE CHARGE_TEXT block to try and add a conditional tagattr, but it is not working.i am attaching the code and output.KIndly help with my ask.

proc report data=BASE_UNBUND_REV_ELEC_12M_MOD nowd spanrows missing
style(report)=[bordercolor=black borderwidth=3pt]
style(header)=[verticalalign=middle backgroundcolor=cx4C7DC4 foreground=white]
style(summary)=[backgroundcolor=black foreground=white fontstyle=roman fontweight=bold bordercolor=black borderwidth=2pt]
;
	by pucgrp;
	
	column pucgrp ratelit ('Rate Literal' ratelit_disp) consumption_dt ('Year' consumption_yr_disp)  ('Month' consumption_dt_disp) (charge_text, (charge));

	define pucgrp / '' group noprint;

	define ratelit / '' group noprint;
	define ratelit_disp / '' computed style(column)=[backgroundcolor=lightgrey];

	define consumption_dt / order=internal group '' noprint;
	define consumption_yr_disp / f=year4. '' computed style(column)=[backgroundcolor=lightgrey];
	define consumption_dt_disp / f=monname3. '' computed style(column)=[backgroundcolor=lightgrey];

	define charge_text / across order=data '' style(column)=[backgroundcolor = white width=40%] ;
	define charge / '' style(column)=[backgroundcolor = white] ;
	
	compute before ratelit;
		hold_ratelit = ratelit;
	endcomp;

	compute before consumption_dt;
		hold_consumption_dt = consumption_dt;
	endcomp;


	compute ratelit_disp / char length=25;
		ratelit_disp = hold_ratelit;
	endcomp;

	compute consumption_yr_disp;
		consumption_yr_disp = hold_consumption_dt; 
	endcomp;

	compute consumption_dt_disp;
		consumption_dt_disp = hold_consumption_dt; 
	endcomp;
	
	compute charge_text;
		if charge_text = 'Billed Months' then 
			do;
				call define('charge','style','style={tagattr="0.00,[Red](0.00),0.00"}');
			end;
		else;
			do;
				call define('charge','style','style={tagattr="$0.00,[Red]($0.00),$0.00"}');
			end;
	endcomp;
	
	compute after ratelit;
		call define ('consumption_yr_disp', 'style', 'style=[tagattr=''formula:=""'']');
		call define ('consumption_dt_disp', 'style', 'style=[tagattr=''formula:=""'']');
	endcomp;
	break after ratelit / summarize; /* Every time the ratelit Changes Summarize the table (SubTotal)*/

run;

This is output im getting. Wherever i have pointed out with the arrow i need to be displayed as style={tagattr="0.00,[Red](0.00),0.00"} and all others as style={tagattr="$0.00,[Red]($0.00),$0.00"}

this is how my data looks

PUCGRP	RATELIT	CONSUMPTION_DT	ID	CHARGE_TEXT	CHARGE
Domestic	D-CARE-SDP-O/BUND	01Jul2020	0	Billed Months	1.50
Domestic	D-CARE-SDP-O/BUND	01Jul2020	0	Billed KWH	5013.97
Domestic	D-CARE-SDP-O/BUND	01Jul2020	0	Billed Operating Revenue	777.39
Domestic	D-CARE-SDP-O/BUND	01Jul2020	1	Generation (Post)	506.27
Domestic	D-CARE-SDP-O/BUND	01Jul2020	2	GTSR Charge	0.00
Domestic	D-CARE-SDP-O/BUND	01Jul2020	3	GTSR Credit	0.00
Domestic	D-CARE-SDP-O/BUND	01Jul2020	4	GTSR Indifference Adj	0.00
Domestic	D-CARE-SDP-O/BUND	01Jul2020	5	PTR/SEP Fixed Capacity Cr-G	0.00
Domestic	D-CARE-SDP-O/BUND	01Aug2020	0	Billed Months	1.23
Domestic	D-CARE-SDP-O/BUND	01Aug2020	0	Billed KWH	2382.29
Domestic	D-CARE-SDP-O/BUND	01Aug2020	0	Billed Operating Revenue	358.22
Domestic	D-CARE-SDP-O/BUND	01Aug2020	1	Generation (Post)	334.46
Domestic	D-CARE-SDP-O/BUND	01Aug2020	2	GTSR Charge	0.00
Domestic	D-CARE-SDP-O/BUND	01Aug2020	3	GTSR Credit	0.00
Domestic	D-CARE-SDP-O/BUND	01Aug2020	4	GTSR Indifference Adj	0.00
Domestic	D-CARE-SDP-O/BUND	01Aug2020	5	PTR/SEP Fixed Capacity Cr-G	0.00
Domestic	D-CARE-SDP-O/BUND	01Sep2020	0	Billed Months	1.77
Domestic	D-CARE-SDP-O/BUND	01Sep2020	0	Billed KWH	1108.08
Domestic	D-CARE-SDP-O/BUND	01Sep2020	0	Billed Operating Revenue	119.81
Domestic	D-CARE-SDP-O/BUND	01Sep2020	1	Generation (Post)	143.42
Domestic	D-CARE-SDP-O/BUND	01Sep2020	2	GTSR Charge	0.00
Domestic	D-CARE-SDP-O/BUND	01Sep2020	3	GTSR Credit	0.00
Domestic	D-CARE-SDP-O/BUND	01Sep2020	4	GTSR Indifference Adj	0.00
Domestic	D-CARE-SDP-O/BUND	01Sep2020	5	PTR/SEP Fixed Capacity Cr-G	0.00
Domestic	D-CARE-SDP-O/BUND	01Oct2020	0	Billed Months	0.50
Domestic	D-CARE-SDP-O/BUND	01Oct2020	0	Billed KWH	276.67
Domestic	D-CARE-SDP-O/BUND	01Oct2020	0	Billed Operating Revenue	28.63
Domestic	D-CARE-SDP-O/BUND	01Oct2020	1	Generation (Post)	24.71
Domestic	D-CARE-SDP-O/BUND	01Oct2020	2	GTSR Charge	0.00
Domestic	D-CARE-SDP-O/BUND	01Oct2020	3	GTSR Credit	0.00
Domestic	D-CARE-SDP-O/BUND	01Oct2020	4	GTSR Indifference Adj	0.00
Domestic	D-CARE-SDP-O/BUND	01Oct2020	5	PTR/SEP Fixed Capacity Cr-G	0.00

PDF Report on One Page

NikitaTovey — Thu, 10 Dec 2020 15:05:52 GMT

Hello SAS Communities,

How can I get the following report to be produced on one page instead of two?

ODS PDF FILE = "&CourseRoot/Documents/Test.pdf";
PROC REPORT DATA = Results.ChiSqResults;
COLUMN Statistic
("Statistical Results" DF Value Prob);
DEFINE Statistic / 'Test';
DEFINE Prob / 'P-Value';
FORMAT Value 4.2
Prob 4.2;
RUN;

PROC PRINT
DATA = Results.ANOVAResults
SPLIT = '/'
OBS = '/Variable';
LABEL
Dependent = 'Variable/Name'
FValue = 'F/Statistic'
ProbF = '/P-Value';
RUN;
ODS PDF CLOSE;

SAS Excel carriage returns in cells

TashaChapWUSS — Tue, 08 Dec 2020 18:06:41 GMT

Hey all,

I'm having this weird issue where my Excel files created with SAS have carriage returns in the middle of the cells. It's like SAS is determining the column width at some point and then inserting carriage returns instead of true text wrapping.

I'd send over some code, but I'm just using a very basic PROC Print in SAS Studio. I have a feeling it's a settings issue. I'm having some vague recollection of a setting I used back when we had EG that prevented this, but I could be making that up entirely.

Has anyone else run across this?

Removing elements of an ODS OUTPUT in SAS Studio

AnnaWoolery — Thu, 10 Dec 2020 15:12:55 GMT

Hi there!

I'm working with a data set that has 573 observations and consists of 10 variables. In the code below, I'm trying to remove some of the elements from my output In the log, I'm getting a blue note that says, "NOTE: ODS statements in the SAS Studio environment may disable some output features." Will the approach (included below) I'm taking not work in SAS Studio?

* Step 2: Perform a Chi-Square Test and Save the Results *;
ODS TRACE ON;
PROC FREQ DATA = work.Analysis;
TABLES HypRelDeathInd * StateCd / NOPERCENT NOROW NOCUM CHISQ;
RUN;
ODS TRACE OFF;

ODS OUTPUT CrossTabFreqs = Rslt.StatePercents
(KEEP = StateCd HypRelDeathInd Frequency Colpercent
WHERE = (HypRelDeathInd = 1 AND MISSING(StateCd) = 0));

ODS OUTPUT ChiSq = WORK.ChiSqResults
(DROP = TABLE
WHERE = (Statistic = 'Chi Square'));

Print Line of Text to Tagsets.ExcelXp is broken into multiple lines

SaugataM — Fri, 04 Dec 2020 12:52:12 GMT

I can use data _null_ and file print, but not matter what, if the text is a bit long it is broken into multiple lines. Also the next line goes into the next worksheet. Below is the code I am running:

ods tagsets.excelxp file='c:\temp\CheckScenario.xls'
options(sheet_name="Tab_Name_here" frozen_headers='5' ascii_dots='no');

Data _null_;		
			 Length Text1 $500. Text2 $500.;
			 Text1 = "Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. To take a trivial example, which of us ever undertakes laborious physical exercise, except to obtain some advantage from it?";
			 Text2 = "Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur.";
			 file print;
				 put #2 @1 "Header goes here";
				 put #3 @1 "The place for Header2 is here ";
				 put #15 @50 "Relevant information goes here. Working";
				 put #16 @1 Text1;
				 put #17 @1 Text2;
run;
ods tagsets.excelxp close;

If you run this code you will get a report that does not follow the correct line structure. In short the Text1 will be broken in two lines and encroach on row number 17. Then the Text 2 is pushed to another tab (created automatically) and starts from that tab's 17th row. How can I fix this and get the texts in single line and maintain line numbers?

Also if I use, option ascii_dots='no' to get rid of the starting period; then line 3 starts from column 1 instead of as mentioned. If I do not use this option then the text start at 50 but I get periods at the start of each row. Any way to fix this?

ODS output dataset not being created

rdevoss — Thu, 10 Dec 2020 15:10:48 GMT

ODS trace on;
proc freq data = data;
tables hypreldeathind*statecd / norow nopercent nocum missing chisq;
run;
ods trace off;

Ods output crosstabfreqs = want (keep = StateCd HypRelDeathInd Frequency ColPercent

 where = (StateCd IN ('IA' 'MS' 'UT') and HypRelDeathInd = 1 and Missing(StateCd = 0)))
 chisq = hyprslt.chisqresults (drop = Table Where = (statistic = 'Chi Square')) ;
 
Proc Print data=data;
run;

Proc contents data = data;
run;

Proc print data=data;
run;

I'm receiving errors that the data tables do not exist. It looks like I'm creating them correctly in ODS output though.

This may be related to warning messages received in log --

 WARNING: Output 'chisq' was not created.  Make sure that the output object name, label, or path is spelled correctly.  Also, verify 
          that the appropriate procedure options are used to produce the requested output object.  For example, verify that the 
          NOPRINT option is not used.
 WARNING: Output 'crosstabfreqs' was not created.  Make sure that the output object name, label, or path is spelled correctly.  
          Also, verify that the appropriate procedure options are used to produce the requested output object.  For example, verify 
          that the NOPRINT option is not used.

Any ideas?

ODS pdf how to set pagesize

MR19 — Thu, 03 Dec 2020 00:38:16 GMT

I'm creating a pdf report with 4 tables and I want to use PAGESIZE to set each table on one page but failed to do that.

I'm not sure where to put the PAGESIZE option and how to determine the number for it.

Here are the codes.

ods pdf file='C:\Users\Box\#4\report2.pdf';
proc print data=q6;
id Guest;
by table;
sum nonbeef;
sumby table;
var food;
title 'Summary of food order by table';
run;
ods pdf close;

ODS OUTPUT ISSUES: WARNING OUTPUT NOT CREATED

Deanna_Payne — Thu, 10 Dec 2020 15:14:41 GMT

Hello All, I need help debugging and understanding errors in my code.

What I want is a permanent dataset named Data.ChiSqResults that looks like this:

Test DF Value P-Value

Chi-Square 2 1.71 0.43

What I start with: See picture ~"Statistics for Table of Death BY State"

But I run my code I get two warnings and an empty dataset. For some Reason, my Ods Output isn't saving to my Work Library and I don't know why. I tried changing locations to the folder Library.Heartattack but that didn't work either.

I tried closing my SAS and that didn't help either.

I was able to get ODS OUTPUT to work one time but I don't know how I did that and it seemed like a goof as it didn't rerun. But even when it did run, I got an empty table (See last picture) as it didn't seem to recognize my IF statement. In addition, I also recieved the sporadic warning: "WARNING: Duplicate data set name on ODS OUTPUT statement. Entry will be ignored." and I don't know what triggers it.

TODO:

Isolate the "Chi-Square" Observation in a new Permanent Dataset (HIGHEST PRIORITY)
Round the Value and Probability to 2 decimal places without trailing zeroes.

CODE

* Step 2: Perform a Chi-Square Test and Save the Results *; RUN;
ODS TRACE ON;
PROC FREQ DATA = heartattack.ANALYSIS2;
TABLES Death * STATE /CHISQ NOROW NOPERCENT;
RUN;
ODS TRACE OFF;

ODS OUTPUT CROSSTABFREQS = WORK.STATES
CHISQ = WORK.CHISQRESULTS;

/* ODS OUTPUT CROSSTABFREQS = Heartattack.CROSSTABFREQS */
/* CHISQ = heartattack.CHISQ; */

DATA WORK.CHISQRESULTS;
SET Heartattack.CHISQRESULTS;
KEEP STATISTIC DF VALUE PROB;
LABEL PROB = "P-VALUE";
IF STATISITIC = "Chi-Square";
VALUE = ROUND(VALUE, 0.01);
PROB = ROUND(PROB, 0.01);
RUN;

PROC PRINT DATA = Heartattack.CHISQRESULTS NOOBS;
RUN;

LOG

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

NOTE: ODS statements in the SAS Studio environment may disable some output features.

74 ODS TRACE ON;

75 PROC FREQ DATA = Heartattack.HYPANALYSIS2;

76 TABLES HYPRELDEATHIND * STATECD /CHISQ NOROW NOPERCENT;

77 RUN;

Output Added:

-------------

Name: CrossTabFreqs

Label: Cross-Tabular Freq Table

Template: Base.Freq.CrossTabFreqs

Path: Freq.Table1.CrossTabFreqs

-------------

Output Added:

-------------

Name: ChiSq

Label: Chi-Square Tests

Template: Base.Freq.ChiSq

Path: Freq.Table1.ChiSq

-------------

NOTE: There were 573 observations read from the data set heartattack.HYPANALYSIS2.

NOTE: PROCEDURE FREQ used (Total process time):

real time 0.89 seconds

cpu time 0.67 seconds

78 ODS TRACE OFF;

80 ODS OUTPUT CROSSTABFREQS = WORK.STATEPERCENTS

81 CHISQ = WORK.CHISQRESULTS;

83 /* ODS OUTPUT CROSSTABFREQS = HYPRSLT.CROSSTABFREQS */

84 /* CHISQ = Heartattack.CHISQ; */

86 DATA WORK.CHISQRESULTS;

87 SET Heartattack.CHISQRESULTS;

88 KEEP STATISTIC DF VALUE PROB;

89 LABEL PROB = "P-VALUE";

90 IF STATISITIC = "Chi-Square";

91 VALUE = ROUND(VALUE, 0.01);

92 PROB = ROUND(PROB, 0.01);

93 RUN;

NOTE: Variable STATISITIC is uninitialized.

NOTE: There were 6 observations read from the data set Heartattack.CHISQRESULTS.

NOTE: The data set WORK.CHISQRESULTS has 0 observations and 4 variables.

NOTE: DATA statement used (Total process time):

real time 0.19 seconds

cpu time 0.17 seconds

95 PROC PRINT DATA = Heartattack.CHISQRESULTS NOOBS;

96 RUN;

WARNING: Output 'CHISQ' was not created. Make sure that the output object name, label, or path is spelled correctly. Also, verify

that the appropriate procedure options are used to produce the requested output object. For example, verify that the

NOPRINT option is not used.

WARNING: Output 'CROSSTABFREQS' was not created. Make sure that the output object name, label, or path is spelled correctly.

Also, verify that the appropriate procedure options are used to produce the requested output object. For example, verify

that the NOPRINT option is not used.

NOTE: There were 6 observations read from the data set Heartattack.CHISQRESULTS.

NOTE: PROCEDURE PRINT used (Total process time):

real time 0.40 seconds

cpu time 0.34 seconds

99 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

111

SCREENSHOTS

how to align left xxx1 and align right xxx2 in a same line(not in a tilte)?

Wen_23 — Sat, 28 Nov 2020 15:19:47 GMT

ods rtf file="D:test.rtf";
ods escapechar='^';
title 'table: using ods in proc report';

proc report data=sashelp.class;
	column sex name  ("BMI Parameters" height weight);
	define sex/order;
	define name/display 'student name' style(column)={asis=on cellwidth=1.5 in just=l} flow;
	define height/analysis format=5.1 style(column)={asis=on cellwidth=1.5 in just=l} flow;
	define weight/analysis format=6.2 style(column)={asis=on cellwidth=1.5 in just=l} flow;

    compute after _page_;
          line "footnote: xxxxx";
		  line "^S={just=left}...path\xxx\xxx.sas" "^S={just=right}[&sysdate9.]";  /*1.doesn't work*/
		  line '^R"\ql"' "...path\xxx\xxx.sas"    '^R"\qr"' " [&sysdate9.]"; /*2.doesn't work*/

    endcomp;

run;

title;
ods rtf close;

Here is the sample code

The final result I want is in the picture.

How to create a table of contents with ODS PDF

Athenkosi — Tue, 24 Nov 2020 12:18:39 GMT

I'm tried to create a table of contents using the example in the SAS documentation (see the example below). I'm not getting the same results my pdf file does not contain a table of contents, please help.

I am using SAS 9.4M4 on SAS EG 7.1

title "Create a Table of Contents";
options nodate;

ods pdf file="pathname-to-MyDefaultToc.pdf" contents=yes bookmarklist=hide;

proc print data=sashelp.cars;
run;

proc print data=sashelp.class;
run;

ods pdf close;

Hyperlinking in Excel using ODS Excel and PROC REPORT

cgsmoak56 — Fri, 20 Nov 2020 18:20:54 GMT

Hi,

I am using ODS Excel and PROC REPORT to generate an Excel spreadsheet with multiple worksheets. I am not using tagsets because my understanding is that ExcelXP with tagsets cannot produce an .xlsx file. So, I am specifically looking for a solution using ODS Excel.

In the Excel spreadsheet there is a worksheet called Datasets, The first column is called Domain and can have 30 to 40 values like AE, CM, DM, DS, etc. Each of these domains (AE, CM, DM, DS, etc) can have there own worksheet in the Excel file with specification for creating the particular dataset (AE, CM, DM, DS). The Datasets worksheet provides a general description of each dataset to be created.

What I want to do is to hyperlinking each domain listed in the Datasets tab to the specific worksheet in the Excel file. For example, I want to be able to click on AE in the Datasets worksheet and have it take me to the AE worksheet in the Excel file.

Thank you in advance for any suggestions. I appreciate it very much.

How to create double lines after a condition in proc report

sas_student1 — Wed, 18 Nov 2020 21:23:33 GMT

Hello!

I am new to Proc report and I tried looking for a solution to my question, and although there were lots of useful information and learned a lot of new techniques while researching, I couldn't find a solution to my specific question, so posting here hoping that someone will be able to assist. (especially since I am new to proc report and still getting a handle on its power!).

My question:

I have a data set that list by ID and Group if a certain ID is present (Y) or absent(null) under five stages. What I want to be able to do is after each grouping of the stage of Y that there be a double line.

So:

My here is the dataset that you can use:

data have;
input ID1 $ 1-3 Group1 $ 4-7 stage1 $ 8-9 Stage2 $ 10-11 Stage3 $ 12-13 Stage4 $ 14-15 Stage5 $ 16-17;
return;
cards;
1  down Y        
1  Up   Y		 
2  Up	Y		 
2  down   Y		 
3  down   Y		 
3  Up	  Y		 
4  Up	  Y		 
4  down    	Y	 
5  down    	Y	 
5  Up	    Y	 
6  Up	   	Y	 
6  down       Y	 
7  down       Y	 
7  Up	      Y	 
8  Up	      Y	 
8  down         Y
9  down 		Y
9  Up	        Y
10 Up		    Y
;
run;

I figured out how to gray out rows for each stage that is a "Y" but cant figure out how to add double lines.

Here is the proc report I have working so far (bonus: You will notice that I have five compute statements that are basically doing the same thing for each stage variables, I wonder if there is a way that I can condense it into one so its not repeating).

ods pdf file="C:\test.pdf";
   proc report data=have nowd;
     column Group1 ID1 stage1 stage2 stage3 stage4 stage5;
     define Group1 / display;
     define ID1 / display;
	 define stage1 / display;
	 define stage2 / display;
	 define stage3 / display;
	 define stage4 / display;
	 define stage5 / display;
   compute stage1;
     if stage1 ='Y' then call define(_col_,"style","style={background=gray}");
    endcomp;

   compute stage2;
	 if stage2 ='Y' then call define(_col_,"style","style={background=gray}");
   endcomp;

   compute stage3;
     if stage3 ='Y' then call define(_col_,"style","style={background=gray}");
   endcomp;

   compute stage4;
     if stage4 ='Y' then call define(_col_,"style","style={background=gray}");
   endcomp;

   compute stage5;
     if stage5 ='Y' then call define(_col_,"style","style={background=gray}");
   endcomp;
   run;
 ods pdf close;

What I would like to be able to add to the proc report codes that indicate to add double line (or even a bold line) after every grouping of the gray cells. See pic of what I would like to have.

Your help would be appreciated!

gplot PNG files stored in a wrong folder

maubry — Tue, 17 Nov 2020 07:48:04 GMT

Hello,

I'm new with SAS. One of my users encounter the following issue:

When he ran a EG Project, the run is blocked when a graph should be produced.

And I don't understand why it is blocked.

I noticed some PNG files (gplotxxx.png) are stored in SAS/Config/Lev1/SASApp.

Do you have any ideas on how I can resolve these issues.

thanks

Pivot table editor no hands excel report

yukki — Sun, 15 Nov 2020 13:09:22 GMT

Hi All,

I am using the below code ,when i run this code i am unable to create excel sheet,is it not possible to create excel sheet when we run ODStagsets+tableeditor options,or else do i have to click, export the reults from HTML page and then save it,what if i want to run the code in PC SAS,can anyone of you please help out.

this code is taken from http://support.sas.com/resources/papers/proceedings12/146-2012.pdf

Actually i have to run all this code on PC SAS,if there is no way in creating excel sheet ,can you please give me some idea's as how it can be achieved.

%let home = C:\HoW\Eberhardt-Kong_13173;

%let solutionHome = &home.\solutions;

%let exerciseHome = &home.\exercises;

%let dataHome = &home.\data;

%let resultsHome = &home.\results;

%let javaHome = C:\\HoW\\Eberhardt-Kong_13173\;

%let javaDataHome = &javaHome.\data\;

%let javaResultsHome = &javaHome.\results\;

/* assign the libraries */

libname data "&dataHome";

options fullstimer source source2;

ods tagsets.tableeditor file="&resultsHome\example1.html"

options(

button_text = "Create PivotTable"

auto_excel = "NO"

pivotrow="product_line"

pivotcol="year"

pivotdata="Profit,Quantity,Total_Retail_Price"

pivotdata_stats=”Sum,Average,Max”

excel_save_file="&JavaResultsHome\example1.xlsx"

quit="yes"

);

Title1 "Example 1 - Create the First Pivot Table";

proc print data=data.sales;

run;

ods tagsets.tableeditor close

titles with ODS Word

Katie_Lane — Thu, 12 Nov 2020 19:43:56 GMT

Trying out ODS Word. I realize it is pre-production. Perhaps I am just writing something wrong. I have 3 different proc prints/ODSTEXT statements each with a unique title. However, I only see the first title showing in the created Word file although in the listing i do see the correct titles. I did turn startpage off to all be on 1 page and when i toggle that back to yes, I do see all 3 titles. Please help me understand why setting startpage=no does not allow me to have separate titles.

ods word file='h:\testing.docx' sasdate options(body_title='yes' toc_data='on') nogtitle startpage=no;

proc print data=fun; title 'Does it print'; run;

proc print data=fun; title '2nd title'; run;

proc print data=fun; title '3rd title'; run;

ods word close;

ODS EXCEL SAS EG 8.2 how to remove space in the output when character variable is blank?

1239 — Wed, 11 Nov 2020 14:18:42 GMT

how to remove space in the output when character variable is blank using ODS EXCEL? I have provided the below sample code and attached excel output file for your quick reference. Kindly note that formula used in "Column C" from test1.xslx file shows that "siteid" has single space inserted but I want to remove this. I am looking for a solution using ODS EXCEL in SAS EG 8.2.

data have ;
  length SITEID $5 SUBJID $20 ;
  infile cards dsd truncover ;
  input siteid subjid ;

cards;
,102-002-001
,102-008-002
,102-008-008
,102-02-002
;
run;

ods excel file="c:\temp\test1.xlsx" ;
proc print data=have noobs label;
run;
ods excel close ;

How to know ODS pdf is on a new page

Jerry8989 — Fri, 06 Nov 2020 14:50:42 GMT

We are trying to conditionally add blank rows so our bookmarks line up. Which after doing that they do, but on new pages we have 2 blank rows. We were hoping to find a way to know if it's a new page and not put the 2 blank rows. In the example below we would put this check after the obj.body_start().

Here is an example of our code:

  obj.table_start(Label:Templabel, overrides: 'frame=void rules=none cellpadding=0in cellspacing=.06in ') ;
      obj.body_start() ;
        obj.row_start() ;
          obj.format_cell(text: " ", column_span: 2,  overrides: "backgroundcolor=white just=left fontweight=bold font_face='Courier New' font_size=9pt ") ;                
        obj.row_end() ;       
        obj.row_start() ;
          obj.format_cell(text: " ", column_span: 2,  overrides: "backgroundcolor=white just=left fontweight=bold font_face='Courier New' font_size=9pt ") ;                
        obj.row_end() ;

Want to add images in the last page of password protected pdf file by using SAS

ShammiKalsi — Mon, 02 Nov 2020 09:36:43 GMT

Dear team,

We are creating password protected .pdf file by using SAS ODS. The size of pdf do vary, like some time pdf file having 2 pages , some time 3,4 and 5 pages.

I want to add images in the last page of password protected pdf file.

Kindly assist !!

Thanks in Advance..

Regards,

Sukhbir Singh

Mob: 95 82 85 38 56

The Output of Paired ttest with weight cannot be written into an Excel file

liziwu — Tue, 27 Oct 2020 19:08:30 GMT

Hi everyone,

I tried to save the output of Paired ttest with weight to an excel file, but I got error message as below:

When the weight statement was removed, the output was written into an excel file. Anyone knows how to correct the issue?

Thanks!

Lizi

Small Caps for ODS PDF

tburus — Tue, 27 Oct 2020 06:51:37 GMT

I am in the process of turning an ODS RTF document into a ODS PDF document for a client and need to find a way to convert small caps. In ODS RTF I just use the tag \scaps. I cannot seem to find an equivalent way of doing this without having to indicate the style font-variant of "small-caps" at every instance. Is it possible to change the template to make all outputs in, say, Heading1 render in small caps?

ODS PDF Equal column width for different tables

AdamD1 — Mon, 26 Oct 2020 10:01:45 GMT

Hi all,

I have a question to improve the visual output of an ODS PDF file.

I'm presenting different tables with PROC REPORT. I could manage to set the width of each table equally. Unfortunately, the columnwidth of both tables are not the same, which looks kinda bad. Is there a way to set the width of each column in each table to one size?

Here is an example code:

ods pdf file="&outpath/example.pdf" style=sapphire startpage=never;
	title "Title";
  proc report data=sashelp.cars style(report)={width=80%};
where MAKE ="Acura";
column Model Origin Type;
run;
proc report data=sashelp.cars style(report)={width=80%};
where MAKE ="Acura";
column EngineSize Horsepower Weight;
run;
ods pdf close;

Thanks in advance.

produce .out file report

null — Mon, 26 Oct 2020 07:26:45 GMT

We are asked to produce a report in .out (extension name) file, rather than .rtf or .pdf.

Does anybody have any experience on this? Thanks for your inputs in adavance!!!

How to set the page margins, font, font size for .out file within sas?

How to generate a solid line for .out file in sas?

computed variables under across variables

js5 — Wed, 21 Oct 2020 14:12:48 GMT

Dear community,

I am trying to collapse median, q1 and q3 for an analysis variable to have them displayed in one cell in the final report. It works as long as I use only group variables, as soon as I switch to across variables I get an error:

ERROR: The variable type of 'MSRP.MEDIAN'n is invalid in this context.

So this works:

proc report data=sashelp.cars;
	columns Origin Make Type msrp msrp=msrpq1 msrp=msrpq3 stats;
	define Origin /" " group;
	define Make /" " group;
	define Type / " " group;

	define msrp / analysis median noprint;
	define msrpq1 / analysis q1 noprint;
	define msrpq3 / analysis q3 noprint;
	define stats / computed;

	compute stats / character length=50;
		stats = catt(put(msrp.median, dollar7.1-l), " (", put(msrpq1, dollar7.1-l), "-", put(msrpq3, dollar7.1-l), ")");
	endcomp;
run;

But this does not:

proc report data=sashelp.cars;
	columns Origin Make Type msrp msrp=msrpq1 msrp=msrpq3 stats;
	define Origin /" " group;
	define Make /" " group;
	define Type / " " group;

	define msrp / analysis median noprint;
	define msrpq1 / analysis q1 noprint;
	define msrpq3 / analysis q3 noprint;
	define stats / computed;

	compute stats / character length=50;
		stats = catt(put(msrp.median, dollar7.1-l), " (", put(msrpq1, dollar7.1-l), "-", put(msrpq3, dollar7.1-l), ")");
	endcomp;
run;

Ultimately I would like to obtain a table similar to one generated by below code, but with median, q1 and q3 collapsed into one computed variable:

proc report data=sashelp.cars;
	columns Origin Make Type, msrp, (n median q1 q3);
	define Origin /" " group;
	define Make /" " group;
	define Type / " " across;
	define n / format=2.;
run;

What am I missing? Thanks!

Adding a background behind table

j-solver — Tue, 20 Oct 2020 19:15:05 GMT

I want to add a simple grey background behind a table in a pdf. I can put a grey bar as a background image for text, but can't seem to do it for a table. Is there an option to create this?

ods pdf text = "^{style [cellwidth=8.5in cellheight=0.41in backgroundimage='D:\Graphics\grey_bar.png'];

proc report data=table 
style(header) = [background=gray foreground=white just=center vjust=center fontsize=&table_font fontweight=bold fontfamily=calibri]
col col_1 col_2 col_3 col_4 obs;

define col_1 /"Column 1" ;
define col_2 /"Column 2" ;
define col_3 /"Column 3" ;
define col_4 /"Column 4" ;
define obs /computed noprint;

compute obs;
obsno + 1;
obs = obsno;

if mod(obs,2) = 0 then 
call define (_row_, 'style', 'style = [background = lightgrey]');
endcomp;
run;

Insert image to proc report

jlee8 — Sun, 18 Oct 2020 18:50:42 GMT

Hi all,

I have been trying to add an image to my pdf report in SAS EG but not been successful. Even when I run the following code which is a sample code for inserting an image, the image does not load and shows only a paper icon with an X on it.

proc report data=sashelp.class(obs=3) nowd
style(report)=
{preimage="http://www.sas.com/images/logos/SAS_TPTK_logo.gif"};
title 'Use URL for PREIMAGE';

Does anyone know what the issue is here?

Thank you very much!

JLee
run;
ods html close;

proc report data=sashelp.class(obs=3) nowd
style(report)=
{preimage="X:\Local\Folder\LOGO.png"};
title 'Use local file for PREIMAGE';
run;
ods html close;

How do I fix ODS PDF error in JCL

kris001 — Sun, 18 Oct 2020 17:25:22 GMT

I have a PS file of FB 200 and I am using SAS (9.4) in JCL to create PDF. Below code I used in JCL.

//SAS.SYSIN DD *
OPTIONS NOERRABEND;
DATA X;
INFILE IN;
INPUT @1 NAME $128.;
RUN;
FILENAME OUTPDF 'TEST.SAS.PDF' DISP=MOD;

ods _all_ close;
ods pdf file='test.sas.pdf';
PROC PRINT DATA=X;
RUN;
ODS PDF CLOSE;

the job is giving return code 08 with SAS log statements as below.

I am unable to figure out what is the error in the statement.

NOTE: The address space has used a maximum of 1052K below the line and 29596K above the line.
2 The SAS System

6 FILENAME OUTPDF 'TEST.SAS.PDF' DISP=MOD;
7
8 ods _all_ close;
9 ods pdf file='test.sas.pdf';
ERROR: .
10 PROC PRINT DATA=X;
11 RUN;

NOTE: Due to ERROR(s) above, SAS set option OBS=0, enabling syntax check mode.
This prevents execution of subsequent data modification statements.
NOTE: The PROCEDURE PRINT used 0.00 CPU seconds and 37787K.

Long list of variables (22) to fit a page

Dinkepile — Wed, 14 Oct 2020 13:10:43 GMT

Good day

I have a long list of variables (22) variable to output on a single page(fit a page) using a proc report. it has been difficult so i opted to divide the listing into 2 parts but retain ID and visit throughout.

Please assist on how i can do this. see below the data:

NUM,ENROLL_DT,IEDAT,DSDAT,DSSTDAT,FASMEDDT,FASOCDT,WEEK0DT,WEEK6DT,WEEK8DT,WEEK16DT,WEEK18DT,WEEK30DT,WEEK32DT,WEEK54DT,WEEK56DT,_page,usubjid1,visit,rand,saffl,cnt
6,16AUG2018,16AUG2018,11SEP2019,16AUG2018,16AUG2018,25MAR2019,16AUG2018,27SEP2018,,,28DEC2018,25MAR2019,,11SEP2019,,1,01,16,Y,Y,1
8,17AUG2018,17AUG2018,18OCT2019,17AUG2018,17AUG2018,23APR2019,17AUG2018,,30OCT2018,28DEC2018,,,23APR2019,,18OCT2019,1,02,16,Y,Y,2
6,20AUG2018,20AUG2018,12SEP2019,20AUG2018,20AUG2018,18MAR2019,20AUG2018,01OCT2018,,,24DEC2018,18MAR2019,,12SEP2019,,1,03,16,Y,Y,3
8,20AUG2018,20AUG2018,12SEP2019,20AUG2018,20AUG2018,26MAR2019,20AUG2018,,15OCT2018,10DEC2018,,26MAR2019,,12SEP2019,,1,04,16,Y,Y,4
8,20AUG2018,20AUG2018,12MAR2019,20AUG2018,20AUG2018,12MAR2019,20AUG2018,,05NOV2018,,,12MAR2019,,,,1,05,16,Y,Y,5
6,20AUG2018,20AUG2018,20SEP2019,20AUG2018,20AUG2018,08MAR2019,20AUG2018,02OCT2018,,,,08MAR2019,,20SEP2019,,1,06,16,Y,Y,6
8,21AUG2018,21AUG2018,03OCT2019,21AUG2018,21AUG2018,02APR2019,21AUG2018,,16OCT2018,11DEC2018,,,02APR2019,,03OCT2019,1,07,16,Y,Y,7
8,21AUG2018,21AUG2018,17SEP2019,21AUG2018,21AUG2018,02APR2019,21AUG2018,,16OCT2018,11DEC2018,,,02APR2019,,17SEP2019,1,08,16,Y,Y,8
8,21AUG2018,21AUG2018,30JAN2019,21AUG2018,21AUG2018,,21AUG2018,,02NOV2018,02JAN2019,,,,,,1,09,16,Y,Y,9
6,21AUG2018,21AUG2018,05APR2019,21AUG2018,21AUG2018,05APR2019,21AUG2018,31OCT2018,,,,05APR2019,,,,1,10,16,Y,Y,10
8,21AUG2018,21AUG2018,01OCT2019,21AUG2018,21AUG2018,09APR2019,21AUG2018,,16OCT2018,11DEC2018,,,09APR2019,,01OCT2019,1,11,16,Y,Y,11
6,21AUG2018,21AUG2018,03SEP2019,21AUG2018,21AUG2018,19MAR2019,21AUG2018,03OCT2018,,,24DEC2018,19MAR2019,,03SEP2019,,1,12,16,Y,Y,12
6,22AUG2018,22AUG2018,04SEP2019,22AUG2018,22AUG2018,20MAR2019,22AUG2018,03OCT2018,,,28DEC2018,20MAR2019,,04SEP2019,,1,13,16,Y,Y,13
6,22AUG2018,22AUG2018,20SEP2019,22AUG2018,22AUG2018,22MAR2019,22AUG2018,03OCT2018,,,27DEC2018,22MAR2019,,20SEP2019,,1,14,16,Y,Y,14
8,22AUG2018,22AUG2018,18SEP2019,22AUG2018,22AUG2018,03APR2019,22AUG2018,,17OCT2018,12DEC2018,,,03APR2019,,18SEP2019,1,15,16,Y,Y,15
8,23AUG2018,23AUG2018,26SEP2019,23AUG2018,23AUG2018,11APR2019,23AUG2018,,22OCT2018,20DEC2018,,,11APR2019,,26SEP2019,1,16,16,Y,Y,16
8,23AUG2018,23AUG2018,08APR2019,23AUG2018,23AUG2018,08APR2019,23AUG2018,,,11DEC2018,,,08APR2019,,,1,17,16,Y,Y,17
8,23AUG2018,23AUG2018,10OCT2019,23AUG2018,23AUG2018,18APR2019,23AUG2018,,19OCT2018,20DEC2018,,,18APR2019,,10OCT2019,1,18,16,Y,Y,18
6,23AUG2018,23AUG2018,02JAN2019,23AUG2018,23AUG2018,,23AUG2018,04OCT2018,,,,,,,,1,19,16,Y,Y,19
6,28AUG2018,23AUG2018,30JAN2019,23AUG2018,28AUG2018,,28AUG2018,08OCT2018,,,,,,,,1,20,16,Y,Y,20
8,28AUG2018,28AUG2018,04OCT2019,28AUG2018,28AUG2018,12APR2019,28AUG2018,,26OCT2018,21DEC2018,,,12APR2019,,04OCT2019,1,21,16,Y,Y,21
8,28AUG2018,28AUG2018,24SEP2019,28AUG2018,28AUG2018,09APR2019,28AUG2018,,23OCT2018,18DEC2018,,,09APR2019,,24SEP2019,1,22,16,Y,Y,22
8,28AUG2018,28AUG2018,25SEP2019,28AUG2018,28AUG2018,09APR2019,28AUG2018,,23OCT2018,18DEC2018,,,09APR2019,,25SEP2019,1,23,16,Y,Y,23
8,29AUG2018,29AUG2018,25OCT2019,29AUG2018,29AUG2018,10MAY2019,29AUG2018,,22OCT2018,21DEC2018,,,10MAY2019,,25OCT2019,1,24,16,Y,Y,24
8,29AUG2018,29AUG2018,30OCT2019,29AUG2018,29AUG2018,14MAY2019,29AUG2018,,09NOV2018,04JAN2019,,,14MAY2019,,30OCT2019,1,25,16,Y,Y,25
8,29AUG2018,29AUG2018,31OCT2018,29AUG2018,29AUG2018,,29AUG2018,,,,,,,,,1,26,16,Y,Y,26
6,03SEP2018,03SEP2018,13SEP2019,29AUG2018,03SEP2018,29MAR2019,03SEP2018,12OCT2018,,,04JAN2019,29MAR2019,,13SEP2019,,1,27,16,Y,Y,27
6,03SEP2018,03SEP2018,16SEP2019,03SEP2018,03SEP2018,01APR2019,03SEP2018,15OCT2018,,,07JAN2019,01APR2019,,16SEP2019,,1,28,16,Y,Y,28

"A component of ... is not a directory" error when exporting multiple sheets using ODS EXCEL

Zoe8SAS — Tue, 13 Oct 2020 23:04:23 GMT

I have a cross-sectional dataset with one observation of a integer variable for each individual. Individuals belong to 3 groups. I want to get the frequency distribution of the integer variable across individuals separately for each group and export the results to one Excel file (called FILENAME) with one sheet for each group. I use ODS EXCEL .

Results for the first group export without issues, but the second group produces the following error:

"A component of PATHNAME\FILENAME.xlsx is not a directory. ERROR: No body file. EXCEL output will not be created." where PATHNAME is the path for the folder where I want to save the Excel file.

Here is a simple example with a SAS Help dataset:

data test;
set 	sashelp.Class;
run;

*Female;
ods excel file="PATHNAME\test.xlsx" 
	options(sheet_name="Female" 
			embedded_titles="yes"
			embed_titles_once="yes");


proc freq data = test(where=(sex="F"));
	label Age='Age at survey';
	tables age;
	title "Age at survey for female participants";
run;


*Male;
ods excel file="PATHNAME\test.xlsx" 
	options(sheet_name="Male" 
			embedded_titles="yes"
			embed_titles_once="yes");


proc freq data = test(where=(sex="M"));
	label Age='Age at survey';
	tables age;
	title "Age at survey for male participants";
run;

ods excel close;

The sheet for female individuals exports without issues, but the sheet for male individuals generates the following error right after the ODS EXCEL statement and before the PROC FREQ statement:

ERROR: A component of PATHNAME\test.xlsx is not a directory.
ERROR: No body file. EXCEL output will not be created.
NOTE: Writing EXCEL file: S:\Projects\COVID19_ESPI\Community Cohort\3_Output\Non_response\test.xlsx

How can I solve this issue?

I saw that for exporting html files defining the path and file separately can work. However, ods excel does not seem to support that option.

Thank you!

Add Text to Date Column Summary

rajesh1980 — Tue, 13 Oct 2020 08:52:58 GMT

Hi ,

I have a ODS Excel SAS PROC report which has Date column and i am group on that column as well. However towards the end i want to add the text 'TOTALS' to this date column summary. But since the column is formatted as DATE it is not displaying the text field. Any inputs on how to achieve this.

This is how my program looks like

proc report data=CUST_REV_MONTHLY nowd spanrows 
style(report)=[bordercolor=black borderwidth=3pt]
style(header)=[verticalalign=middle backgroundcolor=cx4C7DC4 foreground=white]
style(summary)=[backgroundcolor=black foreground=white fontstyle=roman fontweight=bold bordercolor=black borderwidth=2pt]

;

	column pstng_dt contract lcontract billdocn stmt_amt_sum kwh_sum oper_amt_sum;

	define pstng_dt / f=mmddyy10. order=internal 'POSTING DATE' group style(column)=[vjust=middle just=left backgroundcolor=cx4C7DC4 fontweight=bold foreground=white];

	define contract / 'CONTRACT' group style(column)=[vjust=middle just=left backgroundcolor=cx4C7DC4 fontweight=bold foreground=white];
	define lcontract / 'LEGACY CONTRACT' group style(column)=[vjust=middle just=left backgroundcolor=cx4C7DC4 fontweight=bold foreground=white];
	define billdocn / 'BILL DOC NO' group style(column)=[vjust=middle just=left backgroundcolor=cx4C7DC4 fontweight=bold foreground=white];

	define stmt_amt_sum / 'STMT REV AMT' style(column)=[backgroundcolor = white tagattr='format:$#,##0.00;[Red]($#,##0.00);0.00;'] ;
	define kwh_sum / 'KWH USAGE' style(column)=[backgroundcolor = lightgrey];
	define oper_amt_sum / 'OPER REV AMT' style(column)=[backgroundcolor = cxABABAB tagattr='format:$#,##0.00;[Red]($#,##0.00);0.00;'] ;

	break after pstng_dt / summarize; /* Every time the pstng_dt Changes Summarize the table (SubTotal)*/

	compute after;
		pstng_dt = "TOTALS" ; /*When everything ends Add the literal 'Total' in the pstng_dt columns*/
	endcomp;

	rbreak after / summarize; /* Finally summarize the whole table (Grand Totals)*/

run;

This is what im getting

This is how i want it

Kindly Help

Regards..

Rajesh

ODS excel

brendanb — Fri, 09 Oct 2020 16:35:59 GMT

Hi, I am working on Linux.

Trying to create a excel spreadsheet with multiple pages. I am running a stored proc via SAS ECM (using grid-action). The stored process essentially run a query and builds a few tables wit various structures, hence the need to write multiple sheets. I am then using _webout as a file destination to download the file to my machine. The procedure works fine with one table. The minute I add a second proc print I get an excel error stating that the format and extension of 'filename.xls' don't math. The file could be corrupt or unsafe. Unless you trust... I click yes to open and then it gives me three choices in an Open XML box, 1. As an XML table, 2. As a read-only Workbook, 3. Use the xml source pane. Any option fails to open the file. I have tried swapping content types, filename extensions, no luck. With the code below, if I choose just to print one table, I still get the format and extension message, but hen I click yes to open it works fine and I get the results. Your help would be appreciated. Thank you in advance. My code:

ods listing close; 
data _null_; 
rc = stpsrv_header('Content-type','application/vnd.ms-excel'); 
run; 

data _null_; 
rc = stpsrv_header('Content-type','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); 
run; 

data _null_; 
rc = stpsrv_header('Content-disposition', "attachment; filename=subpoena_&caserk._txns.xls"); 
run; 

ODS excel FILE=_webout style=plateau OPTIONS(SHEET_NAME="Class"); proc print data=FITACCTSTXNS; 
run;quit; 

ODS excel FILE=_webout style=plateau OPTIONS(SHEET_NAME="Class2"); proc print data=PACS_057JTXNS; 
run;quit;

 ods excel close ;

How can i filter rows with a value with ods excel?

MIsabel — Fri, 09 Oct 2020 09:20:14 GMT

Hi,

I want to extract some information for the table sashelp.shoes in a excel file with the bellow instruction but I also want to filter the column "Product" (second column) for the values "Sandal" and "Boot" and at the end create an excel with all the observations and the table filter by the column "Product"

I want to have all observations but the default view to be one with just Sandal/Boots and if i want in the Excel see the other values for that column

How can I update my program to have all the observations and also included the filter?

ods excel file="C:\Reportes\Testshoes.xlsx" options(sheet_name="Test");
ods excel options (autofilter = 'all');
proc report data=sashelp.shoes; columns _all_;
run;
ods excel close;

If I update proc report as the bellow line, it's only export the rows with these values, and I need to export all the lines in the excel and selected the filter with these values in the Excel.

proc report data=sashelp.shoes(where=(product="Sandal" or product='Boot'));

I have updated my program and now it creates the filter but it's not executed when i open the excel and I don't know how it can be executed in an automatically way by the ods option and also i would like to include in the filter more values as Boot.

ods excel file="C:\Reportes\Testshoes.xlsx" options(sheet_name="Test");

ods excel options (autofilter='2' autofilter_values='Sandal');
proc report data=sashelp.shoes; columns _all_; run;
ods excel close;

Thanks in advance for your help

Color background of group name in proc report

Knopperoni — Mon, 05 Oct 2020 19:05:05 GMT

Good afternoon. I've been looking for a solution to this problem for a while and have had no luck. I will start by saying that, although I've used sas for a while now, the functions of proc report are still rather new to me. That said, I have a group of variables that I wanted 2 headers for. I was hoping to find a way to just color the background of the top header (Names) and not the entire header row.

data person;
   input name $ dept $;
   datalines;
John Sales
Mary Acctng
;
run;

proc report data=person out=stuff
style(report)={font_size=8pt} 
style(header)={font_weight=bold borderwidth=0 borderspacing=0 font_size=9pt };
COLUMN ("All Data" ("Names" Name) ("Department" Dept));

define Name / display style(column)=[cellwidth=1.2in];
define Dept / display style(column)=[cellwidth=1.2in];
run;

I've tried using call define, but can't figure out how to use that method to target just the cell that the header appears in. I've also tried to manipulate the style in the column statement to no avail. Hoping there's a rather easy method to this madness that doesn't require me to be an advanced coder to sort out. Any help you can provide is greatly appreciated.

ODS RTF coumns jumped to second page while re running proc report

s_manoj — Sun, 04 Oct 2020 14:27:20 GMT

Hi All,

I have an issue with RTF output, I have a data with 4 columns and rows that are enough to fit in one page,

in proc report assigned cell width for the column1 as 4.5 in and rest 1 in each and looks good on RTF after executing the program, and the problem starts when I rerun the same program on a fresh session then page margins on rtf were narrowed and the last column is moved to second page, but this issue is getting rectified immediately after re-running the code on the same session, Can someone please explain why this is happening while running the prog for the first time on a new session.

Please note: Page margins were assigned as

options leftmargin = rightmargin= topmargin= bottommargin= , on top of the proc report code

and style that I have selected for reporting is styles.rtf

Any help would be very much appreciated and that means a lot to me

Thanks in Advance

Manoj

PROC ODS - Highlighting a cell

DLROW — Thu, 01 Oct 2020 02:57:28 GMT

Hi - I need to highlight a variance (cell) that is >=15% or <=-15%. Below is the sample and code. Please help. Thanks.

ODS EXCEL;

PROC REPORT Data=DATA1

COLUMNS First Group Second Group Date_MMYYYY, TOTAL_COUNT Date_MMYYYY,VARIANCE;
define FIRST GROUP / group 'First group' ORDER = INTERNAL;
define SECOND GROUP / group 'Second Group' ORDER = INTERNAL;
define DATE_MMYY / across '';
define TOTAL_COUNT / analysis '' format=comma20.;
define VARIANCE / analysis '' format=percent8.2;

Run;
title;

ods excel close;

Insert blank line to separate horizontal borders

IvanCh — Tue, 29 Sep 2020 08:17:31 GMT

Hi, everyone. Could help me with my output?

i should insert blank lines to separate horizontal borders.

this is my code:

ods pdf style=sasdocprinter file="/home/u35846133/outputs/test1.pdf";

proc report data=sashelp.cars nowd spanrows;
	column make model type MSRP invoice;
	define make /group;
	define model /display;
	define type /display;
	define MSRP /display;
	define invoice /display;
	
compute before make;
	line " ";
endcomp;
run;

ods pdf close;

Exporting Header-Only (no Observations) to Excel

SB_145 — Mon, 28 Sep 2020 13:47:17 GMT

I am looking for a way to export only the header of a dataset to Excel. No matter whether I tried, from starting with an empty dataset to using a filter right before the export, SAS doesn't export the tab that contains a header only.

Any suggestions would be highly appreciated.
Thank you!