Hi, I have a working SAS macro that writes part of PROC SQL code, which I use it to vertically stack multiple datasets that have the same data structure as a single dataset. An oversimplified of using the macro as the following: proc sql; create table work.test_data as %PROC_SQL_script_generator(input_data=BCVA, parameter1=BCVAALL, parameter2=BCVAYN, parameter3=%STR(, BCVAYNSP as parameter3)) union all %PROC_SQL_script_generator(input_data=CHYP, parameter1=CHYPALL, parameter2=CHYPYN, parameter3=%STR(, CHYPYNSP as parameter3)) union all %PROC_SQL_script_generator(input_data=CS, parameter1=FCNSALL, parameter2=CSYN, parameter3=%STR(, CSYNSP as parameter3)) union all %PROC_SQL_script_generator(input_data=FCS, parameter1=FCRSALL, parameter2=FCSYN, parameter3=%STR(, FCSYNSP as parameter3)) order by subjectid, eventname, parameter1; quit; I am looking to make the macro more flexible so that it could pass SAS code to the macro definition. I thought I would be able to pass part of my PROC SQL code through the use of the %STR() function, however, I got stuck when I specify a parameter using a previously specified parameter as the following code: proc sql; create table work.test_data as %PROC_SQL_script_generator(input_data=BCVA, parameter1=BCVAALL, parameter2=%STR(&parameter1. as newvar length=200), parameter3=%STR(, BCVAYNSP as parameter3)) union all %PROC_SQL_script_generator(input_data=CHYP, parameter1=CHYPALL, parameter2=%STR(&parameter1. as newvar length=200), parameter3=%STR(, CHYPYNSP as parameter3)) order by subjectid, eventname, parameter1; quit; This results in a WARNING saying Apparent symbolic reference parameter1 not resolved and the new variable generated by parameter2 has incorrect values. I have tried a number of other functions like %QUOTE, %NRSTR% but none of them work.   Although I am unable to provide you with a sample data for testing, the following example maybe able to mimic the usage above %macro test(v1=,v2=); %put &v2.; %mend; %test(v1=BCVAALL,v2=%STR(&v1. as anotherVar)); As the following output shows, the macro calling above reproduces the WARNING (line 43) but the value of &v2 seems to be correctly printed (line 44) If it is not appropriate to reference a previously specified parameter in the same macro, my last resort would be to just type up the variable names like proc sql; create table work.test_data as %PROC_SQL_script_generator(input_data=BCVA, parameter1=BCVAALL, parameter2=%STR(BCVAALL as newvar length=200), parameter3=%STR(, BCVAYNSP as parameter3)) union all %PROC_SQL_script_generator(input_data=CHYP, parameter1=CHYPALL, parameter2=%STR(CHYPALL as newvar length=200), parameter3=%STR(, CHYPYNSP as parameter3)) order by subjectid, eventname, parameter1; quit; Any comments would be highly appreciated. Chang     ... View more

Hi, I am a R programmer recapping SAS programming. I usually check the existence of folders and files in the beginning of my R program. I am looking to do the same in my SAS program. I found a working SAS macro that checks the existence of a single path. In my example, it is called %check_existence_single_directory. I've used the macro to check the existence of 3 folders that are pre-existing. All of them are found with no problems. I then attempt to tweak the macro as %check_existence_directory in my example,  to include all the directories I want to check in the macro parameter. This macro is working on the first two paths, but not on the last path which contains special characters period and dashes. It ends up creating new folders. Any idea how I can change the macro or specify the paths that SAS won't stop reading when it encounters a period?    Copying my code and log     /*Set up local directory*/ %let drive_E= E:\Lab_MarkS\lunC_work ; %let folder_path_SAS_macro= &drive_E.\library_genetics_epidemiology\SAS_macros_chang ; %let folder_path_Exp108= &drive_E.\Immunohistochemistry_images\data_output\AP_Exp108.1_PeterMac-lungCancer-CD8-PD1\analysis-results ; /*SAS macro to check existence of a single directory (working)*/ %macro check_existence_single_directory(dir=) ; *options noxwait; %local rc fileref ; %let rc = %sysfunc(filename(fileref,&dir)) ; %if %sysfunc(fexist(&fileref)) %then %put NOTE: The directory "&dir" exists ; %else %do ; %sysexec md &dir ; %put %sysfunc(sysmsg()) The directory has been created. ; %end ; %let rc=%sysfunc(filename(fileref)) ; %mend check_existence_single_directory ; /*Check existence of folders*/ %check_existence_single_directory(dir=&drive_E.); %check_existence_single_directory(dir=&folder_path_SAS_macro.); %check_existence_single_directory(dir=&folder_path_Exp108.); Here is the log for the code above   NOTE: The directory "E:\Lab_MarkS\lunC_work" exists 291 %check_existence_single_director(dir=&folder_path_SAS_macro.); NOTE: The directory "E:\Lab_MarkS\lunC_work\library_genetics_epidemiology\SAS_macros_chang" exists 292 %check_existence_single_director(dir=&folder_path_Exp108.); NOTE: The directory "E:\Lab_MarkS\lunC_work\Immunohistochemistry_images\data_output\AP_Exp108.1_PeterMac-lungCancer -CD8-PD1\analysis-results" exists Here is the macro I modifed (not working on the third path)     /*SAS macro to check existence of multiple directories (working?)*/ %macro check_existence_directory(paths=) ; *options noxwait; %local rc fileref ; /*Loop thru each macro parameter value using do loop*/ %do i=1 %to %sysfunc(countw(&paths.)); /*Assign current directory to a macro variable path*/ %let path=%scan(&paths.,&i.); /*Assign the external path to a file reference then to a new macro variable rc(?)*/ %let rc = %sysfunc(filename(fileref,&path.)) ; %if %sysfunc(fexist(&fileref)) %then %put NOTE: The directory "&path." exists ; %else %do ; /*If the path is not found, create that directory*/ %sysexec md &path. ; %put %sysfunc(sysmsg()) The directory has been created. ; %end; %let rc=%sysfunc(filename(fileref)) ; %end; /*Close the do loop*/ %mend check_existence_directory ; /*Calling the macro to check the 3 paths*/ %check_existence_directory(paths=&drive_E. &folder_path_SAS_macro. &folder_path_Exp108.) ; %check_existence_directory(paths= %STR(&drive_E. &folder_path_SAS_macro. &folder_path_Exp108.)); Here is the log for the code above     NOTE: The directory "E:\Lab_MarkS\lunC_work" exists NOTE: The directory "E:\Lab_MarkS\lunC_work\library_genetics_epidemiology\SAS_macros_chang" exists WARNING: Physical file does not exist, E:\Lab_MarkS\lunC_work\Immunohistochemistry_images\data_output\AP_Exp108. The directory has been created. WARNING: Physical file does not exist, C:\Users\lunC\1_PeterMac. The directory has been created. WARNING: Physical file does not exist, C:\Users\lunC\lungCancer. The directory has been created. WARNING: Physical file does not exist, C:\Users\lunC\CD8. The directory has been created. WARNING: Physical file does not exist, C:\Users\lunC\PD1\analysis. The directory has been created. WARNING: Physical file does not exist, C:\Users\lunC\results. The directory has been created. Apparently, the third path is incorrectly read by SAS. How do I improve that? Paths are created according to the group's need. Don't expect non-programmers to know why it is a bad idea to include special characters in the folder.   Thanks               ... View more

Hi,   I am trying to add page numbers to the footnote section on every page of a RTF file. This RTF has a table of contents and then multiple tables. My page will not start with 1, as the RTF file is to be appended to another file that has 213 pages.   I am able to replicate the result from this post Page number at top and page break after each proc report entry and to change the pageno= option to a number other than 1. It works in both PDF and RTF files. I've tried to modify my own script files by adding this to my master script options PAGENO=214 and      FOOTNOTE J=center font='Times New Roman' h=10pt "^{thispage}" ; to individual table script files that are executed by the master script. Unfortunately, adding the pageno=214 has no effect on the starting page number. My starting page number always appears as 1. I don't know if this has something to do with my table of contents. I have 19 table script files to be run within the master script file. I hope there is no need to hard-code page number in each table script file. Any working code would be highly appreciated. Thanks       My master script that generates just 1 table:   options PAGENO=214 /*Starting page number for the next page*/ missing = ' ' /*Specifies the character to print for missing numeric values.*/ center /*Specifies whether to center or left align SAS procedure output*/ nodate /*suppress default print of data time that this SAS program executed*/ nonumber /*nonumber: suppress default print of page number on first title line of each page*/ orientation= landscape /*orientation=portrait */ ; TITLE1; TITLE2; /*------------------------------------------------------changing table margins------------------------------------------------------*/ options bottommargin = 2 cm topmargin = 2 cm rightmargin = 2 cm leftmargin = 2 cm ; /*suppress the warning that a quoted string that exceeds 262 characters in length*/ options NOQUOTELENMAX ; /*Store table title text CSV file in SAS macro variables and values*/ %include "&SAS_script_dir.\supp-table-title-text_to_SAS-macro-var.sas" ; %include "&SAS_script_dir.\import_supplementary-table_table-of-contents.sas" ; /* first tell ODS what character will be used as the ODS ESCAPECHAR character value*/ ods escapechar='~'; ods escapechar='^'; ods _all_ close; ods rtf file= "&report_dir.\PhD-thesis_supplementary-tables_%sysfunc(date(), yymmdd10.).rtf" style= Journal contents toc_data /*contents toc_data: show table of contents*/ ; /*------ Table 1 script---------*/ %let table_order = 1; %let tData_uniVarBin_t1t2Count= output.manu1_count_twin_paris ; %let tScript_uniVarBin_t1t2Count= Ch2_tabSup01_count-twin-pairs_binary-outcomes; %include "&destin_script_dir.\&tScript_uniVarBin_t1t2Count..sas"; /*------------------ close ODS RTF device----------------------------------------------------*/ ods rtf close; ods listing;     My Table 1 script:   /*---------------------------------------------------------------import data into SAS-------------------------------------------------------*/ data output.chapter2_tableSup&table_order.; /*old table name: out._NU_table_08b3 tem._NU_manuscript01_table01*/ set &tData_uniVarBin_t1t2Count. ; gap01=''; /*add gap01, gap02 for inserting blank in PROC REPORT*/ gap02=''; gap03=''; /*Create a dummy variable to remove third node. This node iscontrolled by the BREAK BEFORE statement*/ report_3rd_node_breaker=1; OBSNO=_N_; run; /*--------------------------------Add titles and footnotes----------------------------------------------------------*/ proc sort data= output.chapter2_tableSup&table_order.; by report_3rd_node_breaker; run; /*Get text for the 1st node of Table of Contents from a SAS macro variable The first node is controlled with the ODS PROCLABEL statement. */ ODS PROCLABEL="&&chapter2_suppTableContent&table_order." ; /*TITLE and FOOTNOTE By default, titles and footnotes are both bold and italic. When you change the font, you also turn off the bold and italic features. You can turn them on by using the BOLD and ITALIC options. There is no option to turn off boldness and italics, so if you wish to turn them off, use the FONT= option. */ /*Add table title using SAS macro variable read from an external CSV file*/ title4 J=L font='Times New Roman' h=10pt "&&chapter2_tableSuppTitle&table_order." ; /*Add page number in the footnote section {thispage} is an internal variable that would automatically count the current page number */ FOOTNOTE J=center font='Times New Roman' h=10pt "^{thispage}" ; /*-----------------------------------------------------------Add table body-----------------------------------------------------------*/ /*The 2nd node of table of contents is controlled with the CONTENTS= option on the PROC REPORT statement. Here CONTENTS="" removed the node entirely*/ proc report data=output.chapter2_tableSup&table_order. contents="" /*Remove default 2nd node from the Table of contents*/ nowd split='|' style(report)={width=100% cellpadding=8px font_face='Times New Roman' font_size=10pt borderwidth=1.5pt /* border thickness*/ background = TRANSPARENT} style(header)={just=left font_weight=bold font_face='Times New Roman' font_size=10pt font_style=roman protectspecialchars = OFF background = TRANSPARENT} style(column)={font_face='Times New Roman' font_size=10pt background = TRANSPARENT} ; /*The COLUMN statement is used to list each report column The breaking variable report_3rd_node_breaker needs to be the first thing on the COLUMN statement to get rid of the Table 1 node. */ column report_3rd_node_breaker varGroup OBSNO depVar gap01 /*insert a gap between columns with two-level headers*/ ("\brdrb\brdrdot\brdrw5\brdrcf1 MZ twin pairs (twin1, twin2)" twinCPair_MZ_0_0 twinCPair_MZ_0_1 twinCPair_MZ_1_0 twinCPair_MZ_1_1 ) gap02 /*insert a gap between columns with two-level headers*/ ("\brdrb\brdrdot\brdrw5\brdrcf1 DZ twin pairs (twin1, twin2)" twinCPair_DZ_0_0 twinCPair_DZ_0_1 twinCPair_DZ_1_0 twinCPair_DZ_1_1 ) gap03 sum_completePairs N_twinNonMissDepVar; /*Each column, in turn, has a DEFINE statement that describes how that column is created and formatted.*/ %def_group( cVar=varGroup /*variable name*/ ,option=group /*specify option=order if to order data alphabeticall; = group if not to order data*/ ,cName=Scale /*column header*/ ,cWide=2 cm ,headerAlign=left /*alignment of header text in a column: left, center, right*/ ,colAlign=left /*alignment of content in a column: left, center, right, d (decimal point) */ ); DEFINE report_3rd_node_breaker / NOPRINT ORDER; DEFINE OBSNO / ORDER NOPRINT; %def_display( cVar=depVar, cName=Diagnosis, cWide=3.5 cm, headerAlign=left, colAlign=left); %def_display( cVar=twinCPair_MZ_0_0, cName=(0, 0), cWide=1 cm, headerAlign=right); %def_display( cVar=twinCPair_MZ_0_1, cName=(0, 1), cWide=1 cm, headerAlign=right); %def_display( cVar=twinCPair_MZ_1_0, cName=(1, 0), cWide=1 cm, headerAlign=right); %def_display( cVar=twinCPair_MZ_1_1, cName=(1, 1), cWide=1 cm, headerAlign=right); %def_display( cVar=twinCPair_DZ_0_0, cName=(0, 0), cWide=1 cm, headerAlign=right); %def_display( cVar=twinCPair_DZ_0_1, cName=(0, 1), cWide=1 cm, headerAlign=right); %def_display( cVar=twinCPair_DZ_1_0, cName=(1, 0), cWide=1 cm, headerAlign=right); %def_display( cVar=twinCPair_DZ_1_1, cName=(1, 1), cWide=1 cm, headerAlign=right); %def_display( cVar=sum_completePairs, cName=Total twin pairs, cWide=2.5 cm, headerAlign=right); %def_display( cVar=N_twinNonMissDepVar, cName=Total twin individuals, cWide=1.5 cm, headerAlign=right); /*Define all the gap variables*/ %def_display( cVar=gap01 ,cName= ,isFmt=N ,cFmt= ,cWide=0.2%); %def_display( cVar=gap02 ,cName= ,isFmt=N ,cFmt= ,cWide=0.2%); %def_display( cVar=gap03 ,cName= ,isFmt=N ,cFmt= ,cWide=0.2%); /*Add horitzontal lines under every occurence of dependent variable column*/ compute varGroup; if varGroup not="" then call define (_row_ ,'style' ,'style={bordertopcolor=cyan bordertopwidth=1}'); endcomp; compute after _page_ /style={just=l font_size=10pt font_face='Times New Roman' borderbottomcolor=white bordertopcolor=black}; endcomp; /*Remove 3rd node in the Table of contents*/ BREAK BEFORE report_3rd_node_breaker/ page contents=""; run;           ... View more

Hi,   I am generating a RTF report with 3 tables. I would like to put rows in bold-face if values in a p value column are lower than a threshold. What I have done is basically to duplicate the p value column, place it as the first variable in a SAS data set, and use it in a COMPUTE block. I ended up having to use the .sum keyword inconsistently in these 3 tables in order to correctly format my rows (i.e. only bold rows when p values are < a threshold). I haven't been able to figure out why such inconsistency. But I think the COMPUTE blocks in these 3 tables may explain why. I am giving a summary of my .sum usage in the three tables. I am hoping someone (Cynthia?) can show me the code that will result in consistent output.    Table syntax form of variable to evaluate Result Table S1 dup_p_value.sum Table rows correctly highlighted Table S1 dup_p_value every Table row in boldface Table S3 dup_rG_p_value.sum Sometimes Table rows correctly highlighted; sometimes an error returned Table S3 dup_rG_p_value every Table row in boldface Table S4 pval_dup Table rows correctly highlighted Table S4 pval_dup.sum an error returned and blank table body   I am copying the COMPUTE block code and a printscreen of a table (Table S1, Table S3, and Table S4). I will copy my full working SAS code at the end as it is lengthy.   Table S1. Here is the SAS cod and output of Table S1 which rows with p values < a threshold are displayed correctly in bold-face   compute SE; /*compute predictor/character;*/ if dup_p_value.sum < 0.0125 then call define(_row_, 'style', 'style=[font_weight=bold]' ); endcomp;       Table S1. Actually the use of .sum keyword is unnecessary, because the purpose of dup_p_value is for display, not summation. However, using dup_p_value in the COMPUTE block resulted in all the rows in boldface   compute SE; /*compute predictor/character;*/ if dup_p_value < 0.0125 then call define(_row_, 'style', 'style=[font_weight=bold]' ); endcomp;     Table S3. Using .sum in the COMPUTE block yesterday returned an error: ERROR: The variable type of DUP_RG_P_VALUE.SUM is invalid in this context. But it is working now.   compute rG_esti; if dup_rG_p_value.sum < 0.001111111 then do; call define(_row_, 'style', 'style=[font_weight=bold]' ); end; endcomp;       Table S3. After the removal of .sum, every row is in bold-face, even for those p values > the threshold.   compute rG_esti; if dup_rG_p_value < 0.001111111 then do; call define(_row_, 'style', 'style=[font_weight=bold]' ); end; endcomp;     Table S4. This table is correctly formatted without the use of .sum   compute method_abbreviated/character ; if pval_dup < 0.0002777778 then do; call define(_row_, 'style', 'style=[font_weight=bold]' ); end; endcomp;     Table S4. Using .sum keyword returns the error: "ERROR: The variable type of PVAL_DUP.SUM is invalid in this context. "   compute method_abbreviated/character ; if pval_dup.sum < 0.0002777778 then do; call define(_row_, 'style', 'style=[font_weight=bold]' ); end; endcomp; and results in blank table   My full code for Table S1 (working)     proc report data=tem._NU_manuscript04_table&table_order. contents="" /*remove default 2nd node*/ nowd split='|' style(report)={width=100% cellpadding=8px font_face='Times New Roman' font_size=10pt borderwidth=1.5pt /* border thickness*/ background = TRANSPARENT} style(header)={just=center font_weight=bold font_face='Times New Roman' font_size=10pt font_style=roman protectspecialchars = OFF background = TRANSPARENT} style(column)={font_face='Times New Roman' font_size=10pt background = TRANSPARENT} ; /*The COLUMN statement is used to list each report column*/ column OBSNO dup_p_value iteration model dep_var_label predictor SE p_value effect_size_95CI ; /*Each column, in turn, has a DEFINE statement that describes how that column is created and formatted.*/ /*Every value of these two variables was displayed rather than just the first occurrence*/ %def_group( cVar=iteration ,option=group ,cName=Model no. ,isFmt=N ,cFmt= ,cWide=1.5 cm ,headerAlign=left ,colAlign=left ); %def_group( cVar=model ,option=group ,cName=Method ,isFmt=N ,cFmt= ,cWide=2.5 cm ,headerAlign=left ,colAlign=left ); DEFINE OBSNO/ ORDER NOPRINT ; /*Set conditional variables to noprint*/ define dup_p_value /noprint; %def_display( cVar=dep_var_label, cName=Outcome,isFmt=N,cFmt=,cWide=2 cm, headerAlign= left, colAlign= left); /*cFmt=$var_licit_substance_use_abb.*/ %def_display( cVar=predictor, cName=Predictor,isFmt=N,cFmt= ,cWide=4 cm, headerAlign= left, colAlign= left); *%def_display( cVar=sample_size, cName=Sample size, cWide=1.5 cm, headerAlign= right, colAlign= right); %def_display( cVar=SE, cName=SE,isFmt=Y, cFmt=5.3, cWide=1.5 cm, headerAlign= right, colAlign= right); %def_display( cVar=p_value, cName=P value,isFmt=N, cFmt=, cWide=1.5 cm, headerAlign=right, colAlign=right); %def_display( cVar=effect_size_95CI, cName=Effect size (95% CI),isFmt=N, cFmt=, cWide=2.25 cm, headerAlign=right, colAlign=right); /*Bold rows if p values survive multiple testing 0.0125 (0.05/4) condition variable dup_p_value must be on the left of the COMPUTE block variable predictor call define(_col_) applies the format on the compute variable column-wide call define(_row_) applies the format across entire row*/ compute SE; /*compute predictor/character;*/ if dup_p_value.sum < 0.0125 then call define(_row_, 'style', 'style=[font_weight=bold]' ); endcomp; compute after _page_ /style={just=l font_size=10pt font_face='Times New Roman' borderbottomcolor=white bordertopcolor=black}; endcomp; run;     My full code for Table S3 (working) proc report data=tem._NU_manuscript04_table&table_order. contents="" /*remove default 2nd node*/ nowd split='|' style(report)={width=100% cellpadding=8px font_face='Times New Roman' font_size=10pt borderwidth=1.5pt /* border thickness*/ background = TRANSPARENT} style(header)={just=center font_weight=bold font_face='Times New Roman' font_size=10pt font_style=roman protectspecialchars = OFF background = TRANSPARENT} style(column)={font_face='Times New Roman' font_size=10pt background = TRANSPARENT} ; /*The COLUMN statement is used to list each report column*/ column gap01 ("\brdrb\brdrdot\brdrw5\brdrcf1 Trait 1" trait1_consortium trait1_substance trait1_name) gap02 ("\brdrb\brdrdot\brdrw5\brdrcf1 Trait 2" trait2_consortium trait2_substance trait2_name) OBSNO gap03 ("\brdrb\brdrdot\brdrw5\brdrcf1 Genetic correlations" dup_rG_p_value rG_esti rG_SE rG_Z_score rG_p_value) ; %def_group( cVar=trait1_consortium /*variable name*/ ,option=group /*specify option=order if to order data alphabeticall; = group if not to order data*/ ,cName=Consortium /*column header*/ ,isFmt=N /*apply a format to the variable or not*/ ,cFmt= /*which format*/ ,cWide=1.25 cm ,headerAlign=left ,colAlign=left ); %def_display( cVar=trait1_substance, cName=Substance, cWide=1 cm, headerAlign= left, colAlign= left); %def_display( cVar=trait1_name, cName=Name,isFmt=N,cFmt= ,cWide=1.25 cm, headerAlign= left, colAlign= left); %def_group( cVar=trait2_consortium /*variable name*/ ,option=group /*specify option=order if to order data alphabeticall; = group if not to order data*/ ,cName=Consortium /*column header*/ ,isFmt=N /*apply a format to the variable or not*/ ,cFmt= /*which format*/ ,cWide=1.25 cm ,headerAlign=left ,colAlign=left ); %def_display( cVar=trait2_substance, cName=Substance, cWide=1 cm, headerAlign= left, colAlign= left); %def_display( cVar=trait2_name, cName=Name,isFmt=N,cFmt= ,cWide=1.25 cm, headerAlign= left, colAlign= left); /*Each column, in turn, has a DEFINE statement that describes how that column is created and formatted.*/ DEFINE OBSNO/ ORDER NOPRINT ; /*Set conditional variables to noprint*/ define dup_rG_p_value /noprint; %def_display( cVar=rG_esti, cName=r^{sub G},isFmt=Y,cFmt=5.3, cWide=0.75 cm, headerAlign=right, colAlign=right); %def_display( cVar=rG_SE, cName=SE,isFmt=Y,cFmt=5.3, cWide=0.75 cm, headerAlign=right, colAlign=right); %def_display( cVar=rG_Z_score, cName=Z score,isFmt=Y,cFmt=5.3, cWide=1 cm, headerAlign=right, colAlign=right); %def_display( cVar=rG_p_value, cName=p value, cWide=2 cm, headerAlign=right, colAlign=right); /*define all the gap variables*/ %def_display( cVar=gap01 ,cName= ,isFmt=N ,cFmt= ,cWide=0.25%); %def_display( cVar=gap02 ,cName= ,isFmt=N ,cFmt= ,cWide=0.25%); %def_display( cVar=gap03 ,cName= ,isFmt=N ,cFmt= ,cWide=0.25%); /*Bold rows if p values survive multiple testing 0.001111111 (0.05/45) condition variable dup_rG_p_value must be on the left of the COMPUTE block variable rG_esti call define(_col_) applies the format on the compute variable column-wide call define(_row_) applies the format across entire row Every row is bolded. why? */ compute rG_esti; if dup_rG_p_value.sum < 0.001111111 then do; call define(_row_, 'style', 'style=[font_weight=bold]' ); end; endcomp; compute after _page_ /style={just=l font_size=10pt font_face='Times New Roman' borderbottomcolor=white bordertopcolor=black}; endcomp; run;   My full code for Table S4 (working)   proc report data=tem._NU_manuscript04_table&table_order. contents="" /*remove default 2nd node*/ nowd split='|' style(report)={width=100% cellpadding=8px font_face='Times New Roman' font_size=10pt borderwidth=1.5pt /* border thickness*/ background = TRANSPARENT} style(header)={just=center font_weight=bold font_face='Times New Roman' font_size=10pt font_style=roman protectspecialchars = OFF background = TRANSPARENT} style(column)={font_face='Times New Roman' font_size=10pt background = TRANSPARENT} ; /*The COLUMN statement is used to list each report column*/ column ("\brdrb\brdrdot\brdrw5\brdrcf1 Exposure GWAS" exposure_consortium exposure exposure_clumping_p1 ) gap01 ("\brdrb\brdrdot\brdrw5\brdrcf1 Outcome GWAS" outcome_consortium outcome ) gap02 ("\brdrb\brdrdot\brdrw5\brdrcf1 IVW Estimators" nsnp OBSNO pval_dup method_abbreviated b se pval) /*ARROW, RIGHTWARDS 2192*/ ; /*Each column, in turn, has a DEFINE statement that describes how that column is created and formatted.*/ %def_group( cVar=exposure_consortium ,option=group ,cName=Sample ,isFmt=N ,cFmt= ,cWide=1.25 cm ,headerAlign=left ,colAlign=left ); %def_group( cVar=exposure ,option=group ,cName=Trait ,isFmt=N ,cFmt= ,cWide=1 cm ,headerAlign=left ,colAlign=left ); %def_group( cVar=exposure_clumping_p1 /*variable name*/ ,option=group /*specify option=order if to order data alphabeticall; = group if not to order data*/ ,cName=P value threshold /*column header*/ ,isFmt=Y /*apply a format to the variable or not*/ ,cFmt= e7. /*which format? Ew. Format writes numeric values in scientific notation. range: 7-32*/ ,cWide=2 cm ,headerAlign=left ,colAlign=left ); %def_group( cVar=outcome_consortium /*variable name*/ ,option=group /*specify option=order if to order data alphabeticall; = group if not to order data*/ ,cName=Sample /*column header*/ ,isFmt=N /*apply a format to the variable or not*/ ,cFmt= /*which format*/ ,cWide=1.25 cm ,headerAlign=left ,colAlign=left ); %def_group( cVar=outcome /*variable name*/ ,option=group /*specify option=order if to order data alphabeticall; = group if not to order data*/ ,cName=Trait /*column header*/ ,isFmt=N /*apply a format to the variable or not*/ ,cFmt= /*which format*/ ,cWide=1 cm ,headerAlign=left ,colAlign=left ); %def_group( cVar=nsnp /*variable name*/ ,option=group /*specify option=order if to order data alphabeticall; = group if not to order data*/ ,cName=n SNPs /*column header*/ ,isFmt=N /*apply a format to the variable or not*/ ,cFmt= /*which format*/ ,cWide=1 cm ,headerAlign=left ,colAlign=left ); /*Set conditional variables to noprint. Place these variables after the group variables*/ DEFINE OBSNO / ORDER NOPRINT; DEFINE pval_dup/display noprint; DEFINE method_abbreviated/display noprint; %def_display( cVar=b, cName= Beta, isFmt=Y, cFmt=7.3, cWide=1 cm, headerAlign=left, colAlign=left); %def_display( cVar=se, cName= SE, isFmt=Y, cFmt=7.3, cWide=1 cm, headerAlign=right); %def_display( cVar=pval, cName= p value, cWide=2 cm, headerAlign=right); /*define all the gap variables*/ %def_display( cVar=gap01 ,cName= ,isFmt=N ,cFmt= ,cWide=0.25%); %def_display( cVar=gap02 ,cName= ,isFmt=N ,cFmt= ,cWide=0.25%); /*Conditionally highlight rows with p value < 0.0002777778 (0.05/180) condition variable pvalueAge must be on the left of the COMPUTE block variable betaEstiAge call define(_col_) applies the format on the compute variable column-wide call define(_row_) applies the format across entire row */ compute method_abbreviated/character ; if pval_dup < 0.0002777778 then do; call define(_row_, 'style', 'style=[font_weight=bold]' ); end; endcomp; compute after _page_ /style={just=l font_size=10pt font_face='Times New Roman' borderbottomcolor=white bordertopcolor=black}; endcomp; run; ... View more

hi,   My SAS code works well when exporting compact tables via ODS RTF and PROC REPORT. However, the exported table can have columns too wide when there are just a few columns in the table. I've tried to change the linesize (OPTIONS LD=80) and cellwidth in my individual DEFINE statements (e.g. DEFINE variable / style(column)={cellwidth= 1cm} ...) Unfortunately, all this setting has no effect on my exported table, which has columns too wide leaving large white space between columns:    I had to save the RTF as a docx and manually adjust the column width:   But it would be great to be able to do this, either reducing the table size or column width, in SAS. I hate to mouse clicking in Microsoft Word.    Here is my SAS macro for writing individual DEFINE statement:   %macro def_group( cVar /*character variable*/ ,option /*option= order : duplicated rows shown as blank, SAS will reorder your data */ /*option= group : duplicated rows shown as blank, SAS doesn't reorder your data */ ,cName /*name of the character variable*/ ,isFmt=N /*apply a format to the variable or not*/ ,cFmt= /*which format*/ ,cWide=25pt /*cell width*/ ,headerAlign=center /*alignment of header text in a column: left, center, right*/ ,colAlign=right /*alignment of content in a column: left, center, right, d (decimal point) */ ,marginRight=0 ,background=white /*color of background in a column*/ ,foreground=black /*color of foreground (i.e. font color) in a column*/ ); define &cVar. / &option. "&cName." order=data %if &isFmt.=Y %then %do; format= &cFmt. /*style(column)={just=left cellwidth= &cWide.}*/ style(header)={just=&headerAlign.} style(column)={just=&colAlign. cellwidth= &cWide. rightmargin=&marginRight. background=&background. foreground=&foreground.}; /*replace order with &option. in the future*/ %end; %else %do; style(header)={just=&headerAlign.} /*style(column)={just=left cellwidth= &cWide.}*/ /*replace order with &option. in the future*/ style(column)={just=&colAlign. cellwidth= &cWide. rightmargin=&marginRight. background=&background. foreground=&foreground.}; %end; %mend def_group; and this is how the macro is called:   %def_group( cVar=target_phenotype, option=group, cName=Target phenotype, cWide=3 cm, headerAlign=left,colAlign=left);   Here are my system options which are set before ODS RTF is run:   options missing = ' ' /*Specifies the character to print for missing numeric values.*/ center /*Specifies whether to center or left align SAS procedure output*/ nodate /*suppress default print of data time that this SAS program executed*/ nonumber /*suppress default print of page number on first title line of each page*/ orientation=portrait /*orientation=landscape*/ LS= 80 /*each line of output to contain no more than 200 characters ( LS stands for "linesize")*/ ; TITLE1; TITLE2; /*------------------------------------------------------changing table margins------------------------------------------------------*/ options bottommargin = 0.5 cm topmargin = 0.5 cm rightmargin = 0.5 cm /*0.5in */ leftmargin = 0.5 cm /*0.5in */ ; /*suppress the warning that a quoted string that exceeds 262 characters in length*/ options NOQUOTELENMAX ; /* first tell ODS what character will be used as the ODS ESCAPECHAR character value*/ ods escapechar='~'; ods escapechar='^'; If the layout is more adjustable in other format (e.g. ODS PDF) than RTF, I will be happy to learn that format.  ... View more

hi,   I have SAS 9.4 installed in my Windows 7. I am a base programmer all the time. When I rebooted my computer yesterday, it automatically installed JMP pro14. That took nearly an hour given the slow wifi here. I then restarted my computer again, the magic happened again. I don't understand why my computer automatically installed JMP without my consent. I don't need JMP at all. I didn't even select JMP when I installed SAS. Please let me know if there is a way to stop this. I am not looking forward to another installation of JMP.   Thanks      ... View more

hi,   I'd like to increase the spacing of my title text of my tables, generated via PROC REPORT and ODS RTF, for the purpose of journal submission. The titles should appear double-double spaced, as the first printscreen shows in the following. I am wondering if it is possible to do this in the TITLE statement in conjunction with RTF control words. There seems no TITLE option that can increase the spacing. I've tried some RTF code but my titles still show the default spacing (single spaced).   I looked up RTF code at this website http://www.biblioscape.com/rtf15_spec.htm      Here is my code. The RTF code is used from title2 to title9. Unfortunately, none of these changed the spacing. /*Adding RTF Control Words To use RTF control words within the TITLE and FOOTNOTE statements, you must set the attribute PROTECTSPECIALCHARS=OFF so that ODS does not try to protect these characters. */ /*RTF control words: http://www.biblioscape.com/rtf15_spec.htm*/ proc template; define style styles.test; parent=styles.rtf; style systemtitle from systemtitle / protectspecialchars=off; end; run; ods rtf body='temp.rtf' style=styles.test; /*all titles appear single-spaced with the following options*/ %let longTitle3lines=Number of complete twin pairs and individual twins (parenthetical) in each zygosity and sex group and across all the groups (sum) from different waves of (1) SPHERE12 questionnaire, (2) diagnostic interviews, and (3) twins who are in both samples (overlapped). SPHERE12 data were collected from three waves NU1, NU2, and NU3. Diagnostic data were collected from two waves NU2 and NU3.; %put longTitle3lines=&longTitle3lines.; proc print data=sashelp.class; title 'A normal title'; title2 "\slmult0 &longTitle3lines. "; title3 "\slmult1 &longTitle3lines. " ; title4 " \pard\slmult2 &longTitle3lines. " ; title5 'another title' ; title6 "\sl1 &longTitle3lines. "; title7 "\sl2 &longTitle3lines. "; title8 ls=20 "&longTitle3lines. "; title9 ls=200 "&longTitle3lines. "; run; ods rtf close;   To illustrate a double-spaced title that I need:     By far my titles appear single-spaced:      Thanks           ... View more

hi,   I am having to change my existing table to a style that is required by a journal Genes, Brain and Behavior. This journal requires tables to be double-spaced, including the title and the report rows, and with a single bold ruling beneath the column headers. I generate all my tables with proc report and ODS RTF. I then save the table files as .docx in Microsoft Word. I haven't found out code relevant to a double-spaced title and bolded bottom border. Although it is not difficult to change line spacing and border thickness in MS Word, it would be great to be able to do so with SAS Base ODS RTF by changing my current SAS code. I am illustrating desired layouts with some screenshots in MS WORD   Double spaced titles: My report titles and footnotes are edited and maintained in a MS Excel files. I read the text into SAS macro variables and use them in the title statement per individual table. An example of my title statement : title4 J=L /*this is the table title*/ font='Times New Roman' h=10pt "&&NU_tableTitle&table_order." ;   An example of my current table title:     What I need is a double-spaced bold title:        How do I change the title statement for this?   Bold bottom border under the column headers   I would like to bold the bottom border (the line beneath zygosity, NU1, NU2, NU3 and All waves) as the following printscreen     Is there any possibility to do this in the column statement? I have had some "brdr" options in the column statement. But I haven't found out one that can change the border thickness. Can anyone provide a complete reference to all the brdr keywords/options? It is quite hard to find them online.   An example of my column statement: column OBSNO zygoNum2 gap01 /*insert a gap between columns with two-level headers*/ ("\brdrb\brdrdot\brdrw5\brdrcf1 SPHERE12" twPN_SPHERE12_NU1 twPN_SPHERE12_NU2 twPN_SPHERE12_NU3 twPN_SPHERE12_NUall ) gap02 /*insert a gap between columns with two-level headers*/ ("\brdrb\brdrdot\brdrw5\brdrcf1 Diagnoses" twPN_Diagnoses_NU2 twPN_Diagnoses_NU3 twPN_Diagnoses_NUall ) gap03 ("\brdrb\brdrdot\brdrw5\brdrcf1 Overlapped" twPN_SPHERE12_n_Diag_dummy) ; /**/ Double-spaced report rows   To add this, I create a variable OBSNO=_N_ in the data step, following some web post, and use this variable in PROC REPORT's column statement, display statement and compute after. All these basically add a blank row between report rows. I don't know if adding a blank line means double-spaced, but it looks like the one that the Journal can accept. I've shown the part of my code relevant to the variable OBSNO:    data tem._NU_manuscript01_table&table_order. ; set &tDat_twinPairs_twinN_SP_diag. ; gap01=''; /*add gap01, gap02 for inserting blank in PROC REPORT*/ gap02=''; gap03=''; OBSNO=_N_; run; proc report data=tem._NU_manuscript01_table&table_order. (options not shown due to length) column OBSNO (options not shown due to length) ; DEFINE OBSNO / ORDER NOPRINT; /*Add blank lines to make double spacing under every report row*/ /*LINE statement here adds a blank row. If text in the quote, a format must be used after the quoted text*/ compute after OBSNO ; line ""; endcomp; run; Thanks         ... View more

hi,   I am running SAS 9.4 TS Level 1M3 on 64 bit Windows 7 PRO. I was wondering how remote files on network drives into SAS installed in my local machine. I am able to view my remote files in my web browser, as the following screenshot shows:   However, SAS is able to show only the local drives (C, D and E here). None of the mapped network drives is shown :     My local drives and network drives can be accessed on my computer:   I have tried to read the remote files in SAS base. But none of the following code works   filename outfile sftp '/mnt/lustre/working/lab_xMan/xMan/PRS_UKB_201711/allelicScoresCompiled/output/uniqSNPs_allUKBs_Release8-HRCr1.1/dosageFam_Release8_HRCr1.1/innerJoinedSNPsByCHRBP_metaDataQCed_Release8_HRCr1.1_AND_GWASQCedChrConcat_UKBAllPhenotypes/sasTest.sas7bat' host="hpcapp01.adqimr.ad.lan" user="xMan"; /*actual username is masked*/ data _null_; file outfile; do i=1 to 10; put i=; end; run; /*access files in mapped drive Z. Not working*/ filename a "Z:\LabData\testdiagIRT.sas7bat"; %put %sysfunc(pathname(a)); data test; file a; run; /*access files in mapped drive Z. Not working*/ proc import datafile= "Z:\LabData\testdiagIRT.sas7bat" out=test dbms=dlm replace; datarow=2; GETNAMES=YES; guessingrows=2328; run; How do I connect SAS to my network drives? Some posts suggest to check SAS/CONNECT. My SAS was installed 2 years ago. I cannot recall if SAS/CONNECT was installed nor could I see such a folder under my C:\Program Files\SASHome   Can anyone provide (a link to) a step by step guide for connecting mapped network drives in SAS Base? I saw some posts about this but it is in SAS EG.   Thanks ... View more