Hello,   I am trying to control the flow of my document by having more control over where page breaks are inserted. I am using proc report and outputting it to ods word. I am aware of  break after groupvar / page This would make a new page after each new groupvar value. Is there a way of having a new page after every second groupvar value without preprocessing the data? I know it can be acheived with preprocessing, e.g. https://support.sas.com/resources/papers/proceedings12/389-2012.pdf Unfortunately, as I am using a NOTSORTED format for display, getting the data in the right order for preprocessing and inserting a manual page break variable is somewhat tricky. Thanks! ... View more

Hello,   I am trying to create a document-wide footnote, in addition to the automatic date and page number ODS already creates. I have moved my report title and footnote to document body using body_title: ods word file="C:\Users\&sysuserid\Downloads\SAS\ods_word_title.docx" options(body_title="yes"); title1 "Ford cars"; footnote1 "from sashelp.cars"; proc print data=sashelp.cars; where Make EQ "Ford"; run; ods word close; SAS has automatically created a header with date and page number. Can I add my own text to the footer, using proc template or similar? Thanks! ... View more

Dear community,   I was wondering if there is a way of dynamically changing the label of proc report column label, using call define or otherwise? Please consider the below example: data cars_unit; set sashelp.cars; length Unit $5; if Origin EQ "Europe" then Unit = "kW"; else Unit = "hp"; run; proc sort data=cars_unit; by Origin; proc report data=cars_unit; by Origin; columns Make Horsepower, (mean std); define make / group; define Horsepower / "Horsepower [hp]" analysis; run; I would like to have the label say "Horsepower [kW]" if the Origin is Europe. I could probably to achieve the desired goal using macros but I was wondering if it can be done using proc report alone. Thank you. ... View more

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! ... View more

Dear community,   I am having what seems to be timing problems with the following code: libname config xlsx "&tfl_output_path_2\config.xlsx" access=readonly; data _null_; set config.graphpad; call execute(macro || '(input_dataset=' || dataset || ', day_bin_variable=' || day_bin_variable || ', day_bin_variable_format=' || day_bin_variable_format || ',where=' || condition ||', output=&tfl_output_path_2\' || output || additional_parameters || ')'); run; libname config clear; The macros causing issues look like: %macro export_for_graphpad_fi(input_dataset=, subject_variable=, result_variable=, day_variable=, day_bin_variable=, day_bin_variable_format=, where=, output=); proc sql noprint; create table data_with_dose as select d.DOSE, r.* from &input_dataset r left join cohort_dose_map d on r.XGROUP EQ d.XGROUP and r.XCOHORT EQ d.XCOHORT where not missing(r.XCOHORT) %if %length(&where) GT 0 %then and &where;; select distinct XGROUP into :xgroup1-:xgroup10 from &input_dataset; run; %do i = 1 %to &sqlobs; ods excel file="&output._%lowcase(%sysfunc(translate(&&xgroup&i, _, %str( )))).xlsx" options(sheet_name="data" flow="tables"); proc report data=data_with_dose split="\" missing; where XGROUP EQ "&&xgroup&i"; columns &day_bin_variable DOSE, (GM UCLGM LCLGM); define &day_bin_variable / " " group exclusive preloadfmt format=&day_bin_variable_format order=data; define DOSE / " " across; define GM / " " group; define UCLGM / " " group; define LCLGM / " " group; run; ods excel close; %end; proc datasets noprint; delete data_with_dose; run; %mend export_for_graphpad_fi; The first time I run the code after starting a new SAS session I am getting a warning stating that &sqlobs is not defined. It appears that there are some timing issues between call execute, proc sql and when the &sqlobs variable is getting defined. How can I work them around? Thanks!   ETA: if we ignore call execute it appears that using &sqlobs with %do loops is supported: https://www.mwsug.org/proceedings/2009/how/MWSUG-2009-H01.pdf, page 6. ... View more

While trying to verify the programmed ODS report by comparing it to calculations done in excel directly, it was pointed out to me that the result are almost always a tiny bit different. I can provide example code if needed, but the general issue is: proc report contains values in percent9.2 format, which are exported to excel using ods excel. While the format with two decimal places is maintained, the underlying number is reduced in precision: 0,445772556636722 is what is stored in the SAS dataset, but only 44,57725566% is getting exported to excel. While one could argue that the difference is insignificant, it is nevertheless a loss of precision and causes comparison with Excel calculation to fail unless rounding is incorporated. The value itself is pre-calculated by proc sql, proc report is only used to show the value. I tried changing the format of the variable on the dataset going into proc report to best32. with proc datasets, but the numbers are still exported with reduced precision. Is there anything which can be done about this? Thank you for the advice in advance.  ... View more

Dear community,   I have been requested to provide a report in which mean, median, max, min and stdev are supposed to be shown in two rows: mean (std) and median(min-max) - and in this format. My existing code relies on having the results in a numeric column, which concatenated statistics are not: %macro characteristics_sql_num(total=); select "MSRP" length=100 as Characteristics, "&summary_function" length=100 label=" " as Value, %if %length(&total) GT 0 %then &total as; Origin, &summary_function(MSRP) format=5.1 as result from sashelp.cars %if %length(&total) EQ 0 %then group by Origin; %mend characteristics_sql_num; %macro characteristics_num; %let summary_functions = mean std min median max; proc sql; create table cars_summary as %do i = 1 %to %sysfunc(countw(&summary_functions)); %let summary_function = %scan(&summary_functions, &i); %if &i NE 1 %then union; %characteristics_sql_num union %characteristics_sql_num(total="Total") %end; ; %mend characteristics_num; %characteristics_num proc report data=cars_summary; columns Characteristics Value Origin, result; define Characteristics -- Value / group; define Origin / across; define result / " " format=best6.; run; I tried inserting the needed rows with break after and compute after, but this unfortunately does not work. Is there a way of generating the needed rows during proc report, or should I rather rebuild my macro to generate the needed concatenations already during proc sql? Thank you for your support in advance. ... View more