I generate a SAS program that updates specific cells in an Excel spreadsheet. I am using the following syntax:   options noxwait noxsync; x '"filename.xlsx"'; data _null_; filename update1 dde 'excel|worksheet1!r107c5'; file update1; put '4938'; filename update2 dde 'excel|worksheet1!r107c7'; file update2; put '3146'; run;   The spreadsheet is updated, but the Excel/worksheet1 must be open and highlighted when the SAS program is executed. If it isn't, there is an error that SAS can't find the file - it doesn't exist to SAS.    Is there a method to update the spreadsheet without opening it first? I would like to schedule the job and have the spreadsheet updated automatically.   Thanks...   JIm A.   ... View more

I am trying to format a PDF TOC. Here is an example of the output: I would like to change the font size, font style, and so on. Also, I would like to change the TOC to read:   Ten-Year Fall Enrollments    Chart1a.....    Chart 1b... and so on.   Any suggestions? Here is a copy of the statements: options orientation = landscape nodate; ods pdf file = "&drive.\&dir.\urp01x.pdf" pdftoc=1 uniform contents=yes; ods escapechar='~'; options nonumber; goptions ftitle='Swissb' fby='Swissb' hby=2.2; options nonumber printerpath=pdf; ods proclabel "Ten-Year Fall Enrollments" ; proc sgplot data=urp.tenyrs_stud pctlevel=group description="Chart 1a: Ten-Year Fall Enrollment by Race/Ethinicity"; vbar year / group=racex groupdisplay=stack; &color5.; label racex = 'Race/Ethincity' year = 'Year'; run; ods proclabel "Ten-Year Fall Enrollments"; proc sgpanel data=urp.tenyrs_stud description="Chart 1b: Ten-Year Fall Enrollment by Gender and Race/Ethinicity"; where gender in ('M','1','F','2'); panelby gender; vbar year / group=racex groupdisplay=stack; &color5.; label racex = 'Race/Ethincity' year = 'Year' gender = 'Gender'; format gender $gender.; run; ods pdf close; ... View more

I am getting the overlay of lines in PROC TABULATE (see output 'example.pdf'). I am producing 57 PDF files with each generating 21 PROC TABULATE tables. Some PDF reports can run to over 270 pages. I had this issue with one set of tables, but corrected it by changing the number of footnotes. The example below occurs on the last page of a table. There is plenty of room for the table. Any ideas of how to correct this?   Here is the SAS code: ods listing close; options orientation = landscape nodate; ods pdf file = "&drive\&dir\program output\20&yr5.-&yr6.\&currnum._fall&yr6.progprofilex.pdf"         STYLE=festival pdftoc=1 uniform; ods escapechar='~'; options nonumber printerpath=pdf;   footnote1 j=l height=3 'CONFIDENTIALITY NOTICE: Data presented in this table are for the sole use of the ---- department responsible for the above curriculum and may'; footnote2 j=l height=3 'contain confidential and privileged information. Any access, use, disclosure, or distribution of these data for purposes other than program'; footnote3 j=l height=3 'review and analysis is unauthorized and prohibited.'; footnote4; footnote5; footnote6 j=left bold "Curriculum &currnum / Page ~{thispage} of ~{lastpage}"           j=center bold "Office of ---------------------------"           j=right  bold "&sysdate";. . . .   ods proclabel="Table 16: First-Time-In-College (FTIC) Cohort Disaggregated Demographics"; proc tabulate data = zfive_yr_ftic missing style=[font_size=4];     where curr = "&currnum";         class year fp_code home_campus gender new_race acadplan;     classlev year / style=[font_size=4];         classlev fp_code / style=[font_size=4];         classlev home_campus / style=[font_size=4];         classlev gender / style=[font_size=4];         classlev new_race / style=[font_size=4];         classlev acadplan / style=[font_size=4];         var count1 / style=[font_size=4];         var wgt / style=[font_size=4];         format gender $gender. new_race $racex. home_campus $home_campus. fp_code $fp_code. acadplan $acadplandeg.;         table acadplan='' *(fp_code='FP' home_campus='CAMPUS' gender='GENDER' new_race='RACE')           all='Total'*(fp_code='FP' home_campus='CAMPUS' gender='GENDER' new_race='RACE'),           year=''*(wgt=''*(sum='N'*f=comma8.0 pctsum<fp_code home_campus gender new_race >='%'*f = 5.1))                           / box=[label='Academic Plan'                      style=[background=beige                             foreground=black                             font_size=4]]                      style=[cellspacing=5]              printmiss misstext='0';     title3 'Table 16: First-Time-In-College (FTIC) Cohort Disaggregated Demographics'; run;     ... View more

I am trying to do a running total of 'n' in a PROC REPORT. I can easily get 'n' and '%' like this: Category Item N  % A 1 100 4.76%   2 200 9.52%   3 300 14.29%         B 1 400 19.05%   2 500 23.81%   3 600 28.57%             2100 100.00%   But I want to get cumulative 'n' and '%' within each category like this: Category Item Cum N Cum % A 1 100 16.67%   2 300 50.00%   3 600 100.00%         B 1 400 26.67%   2 900 60.00%   3 1500 100.00%   I have found several papers/references on cumulative totals and percents, but they all use one of the dataset variables. SAS doesn't  recognize the 'n' statistic in a compute block. I have tried doing counts in a compute block - 'cnt+1;' - but didn't work. I could do the counts in a data step, but I would rather do it in the procedure. Any suggestions?   Thanks... Jim A. ... View more