Maybe play around with the WORK= options with the ODS EXCEL statement? For example, only specify a filename with  FILE= and specify your output directory in the WORK= statement? Trying to trick SAS here. ... View more

@PaigeMiller wrote: I have written a macro, and it works fine until the ODS EXCEL command. The macro extracts some data from data bases, based upon macro arguments, does some calculations, and then tries to creates a table via ODS EXCEL. I have removed from this example the stuff that works properly, as there are no errors in the log.     %macro monitoring2(startdate1=,enddate1=,startdate2=,enddate2=, no_rates_lastXweeks=2,graphics_only=0,debug=0,outxlsx=,acaps=1); %put; %put &=outxlsx; %put; ods excel file="&outxlsx" options(sheet_name='Data'); proc report data=_stats1a_; columns method week _freq_ booking_rate approval_rate conversion_rate exception_rate; define method/group 'Method' order=data; define week/group "Week" format=wff. order=internal; define booking_rate/display 'Booking Rate'; define _freq_/display 'N Apps' format=comma10.0; define approval_rate/display 'Approval Rate'; define conversion_rate/display 'Conversion Rate'; define exception_rate/display 'Exception Rate'; compute week; if week=1e10 then call define(_row_,'style','style={color=verydarkblue fontweight=bold background=lightyellow}'); endcompute; run; ods excel close; %mend; %monitoring2(startdate1=01JUL19,enddate1=31JUL19, startdate2=01AUG19,debug=1,graphics_only=1, outxlsx=&pers\ias2\testplots3.xlsx,acaps=0)     Now, here's the log. Note that the %PUT statement shows that the macro variable &OUTXLSX has the desired value, it hasn't changed (shown in red). The error occurs in the ODS EXCEL command, and not in the PROC REPORT. The file name, which is correct in &OUTXLSX, has C:\Users... etc. prepended to the value of &OUTXLSX, and of course, there is no such folder once the mysterious addition to &OUTXLSX happens. Why?     9503 %monitoring2(startdate1=01JUL19,enddate1=31JUL19, startdate2=01AUG19,debug=1,graphics_only=1, outxlsx=&pers\ias2\testplots3.xlsx) OUTXLSX=G:\Team12\Credit Risk Oversight\Retail Risk\Users\MillerP\ias2\testplots3.xlsx MPRINT(MONITORING2): ods excel file="G:\Team12\Credit Risk Oversight\Retail Risk\Users\MillerP\ias2\testplots3.xlsx" options(sheet_name='Data'); NOTE: Writing EXCEL Body file: G:\Team12\Credit Risk Oversight\Retail Risk\Users\MillerP\ias2\testplots3.xlsx ERROR: A component of C:\Users\eccrpm0\AppData\Local\Temp\SAS Temporary Files\_TD16364_PCVPTWX6VP00119_\_T00000000069D5120\G:\Team12\Credit Risk Oversight\Retail Risk\Users\MillerP\ias2\testplots3.xlsx is not a directory. ERROR: No body file. EXCEL output will not be created.     Just for completeness, the macro variable &pers in the call to the macro resolves as G:\Team12\Credit Risk Oversight\Retail Risk\Users\MillerP, so &OUTXLSX does indeed have the desired folder location. Maybe this note is applicable: http://support.sas.com/kb/15/046.html   and this (they seem related): http://support.sas.com/kb/61/280.html   If you are using z/OS you might be out of luck: http://support.sas.com/kb/62/838.html   ... View more

@SR79 wrote: Thanks. The prpc ttest can give me noninf but it's not giving me Farrington Manning CIs. Do you know if there's a function call I can do to incorporate the FM? Just use the CIs output by proc ttest. Rows 1 and 2 in table 4 discuss how to use the standard CIs to determine if the inferiority hypothesis should be rejected.       ... View more

@SR79 wrote: Hello   I am comparing two proportions for non-inferiority and I know I can use proc freq to do farrington manning, but I would like to be able to use proc ttest so that I would have:    proc ttest data=xx h0=noninf_margin alpha=xx; class group; var xx; run;    Is there an option to do method=farrington manning in this code?   Thanks!   This seems to have examples of what you want to accomplish: https://support.sas.com/resources/papers/proceedings15/SAS1911-2015.pdf   see table 4       ... View more

@Denali wrote: Hi,   Can you please help me with generating the randomization lists based on the below study design?   Phase 1:  766 subjects randomized into 2 groups (high dose vs. low dose) (n= 383 per group)   Then we randomly select 268 subjects out of the 383 subjects in low dose group and randomly select 268 subjects out of the 383 subjects in high dose group   Phase 2: we randomize the 268 subjects into 2 groups (no intervention vs intervention) (n=134 per group) among low dose groups and randomize the 268 subjects into 2 groups (no intervention vs intervention) (n=134 per group) among high dose groups   The randomization permuted blocks in phase 1 and phase 2 will also need to be stratified by 4 study sites and gender so I will have 8 lists.   Thank you! You have 4 treatment combinations: High, no internvention/ High, intervention/ Low, no intervention / Low, intervention and you want 134 in each group. You have a total of 766 subjects. 766-134*4=766-536=230.     For a single stratum you can do this. Proc surveyselect data=mydata groups=(134,134,134,134,230) seed=yourseed out=myassignments; run; The output data set will contain all records in your input data set and a variable named GroupID that has values 1-5 (in this example.) That will randomly assign records to 5 groups, numbered 1,2,3,45. You can discard the 5th group. I'd apriori assign group values 1-4 to your 4 treatment options so you avoid assigning groups to treatments based on some biased assessment.   If you have the same number of observations in each stratum, you can do this: Proc surveyselect data=mydata groups=(134,134,134,134,230) seed=yourseed out=myassignments; strata mystrata; run;   Caveat: I don't know exactly how SAS is doing the random assignment. It could be doing a random sort and then a sequential assignment to groups or something else.  If you aren't comfortable with this you can do a sequence of without replacement sampling steps:     /* Phase 1 */ /* For a single stratum */ proc survey select data=mydata method=SRS sampsize=383 seed=myseed out=myresults; run; All observations sampled and placed into myresults should be assigned to either High or Low (make that decision prior to selecting the sample). All observations not inclued in myresults get assigned to the other group.   One you have created an indicator of the assignment to High and Low, do the following: proc survey select data=mydata method=SRS sampsize=268 seed=myseed out=myresults2; strata HighLow ;  /* This variable classifies the 766 subjects into High and Low groups */ run;   The myresults2 data set will contain 268*2=536 records.   /* Phase 2 */ proc survey select data=myresults2 method=SRS sampsize=134 seed=myseed out=myresultsInt; strata HighLow ; run; All records placed into myresultsInt are assigned to Intervention. Those that are not are assigned to non-intervention.       ... View more

@Cynthia_sas wrote: Hi: ODS Graphics Designer is a data driven graphic report tool to generate multi-cell graphs. cynthia   ODS Graphics Designer is only available if you have SAS installed locally. For people using SAS EG with SAS installed on a remote server this doesn't work. (at least according to this page: http://support.sas.com/kb/42/addl/fusion_42604_1_odsgraphicsdesignerwithsaseg43.pdf )   ... View more

@surajmetha55 wrote: Dear all,              I am trying to import Excel file using 2 different program and also tried using SAS import wizard but non of them help me out :    Program 1:   proc import datafile="W:\auto\Trial.xlsx" out = T dbms = xlsx ; sheet="TI"; getnames=yes; run; gives all observation but warning message in log :   WARNING: Some character data was lost during transcoding in column: IETEST at obs 3.   Program 2:   libname sur pcfiles path="W:\auto\Trial.xlsx" ; data ag; set sur.'Ti$'n; run; Both of the program are not able to read "β" (Beta-symbol) and superscript number come to base.   Can you please help me to deal with importing "xlsx" without changing special character.     Sample excel data     You might try changing your SAS session encoding to UTF-8. If your SAS session encoding is already UTF-8 then I'm not sure why you are having the issue unless proc import is somehow treating the file as having Latin encoding.         ... View more

@ChrisNZ wrote: @DWilson  > I think this forum is a fine place for people to ask for VBA help now that SAS has pushed VBA as the alternative to DDE. Would you tell posters in the past who asked about DDE, before it was put to pasture, to go to a Microsoft forum? I would hope not.   We'll have to agree to disagree. I responded after no reply was given for 4 days. VBA skills are plentiful on MS forums (of course) and rare here. There is no doubt that an answer would have been swiftly given on these forums. Same thing for DDE questions involving MS scripts. Or for VBS questions. So the best advice imho is the advice I gave.          Sure. I wholeheartedly agree that VBA skills are plentiful on MS forums and rarer here. But I think SAS put themselves into the position of expanding the set of SAS users who need VBA because they've pushed a change to their technologies that put the onus on VBA to fill the gap left by their removal of DDE support. It shouldn't be unexpected to see more VBA questions here.    I suspect this is more of a generational change (younger folks opting to use VBA) in SAS use.   I also suspect that the set of SAS users/business that need the level of fine-grained control over excel reports is a relatively small fraction over the set of SAS users who produce reports with Excel. This is also a reason why most posters seem to not care about the deprecation of DDE support with SAS  (not to mention the loss of DDE support with base SAS for the SAS grid environment being pushed by SAS.)   p.s. Fwiw, the removal of DDE support without providing a concomitant replacement functionality is the windmill to my Don Quixote. So, pre- and post- apologies if I come across as earnest.  If SAS simply added the ability to write to a single cell in an existing excel spreadsheet without touching the other cells in the spreadsheet (e.g. supporting the use of table templates in excel) then that would address my concern. Asking users to learn VBA and pull data into Excel from SAS to replicate this functionality is silly. A multi-billion dollar company should be able to do something better.     ... View more

@ChrisNZ wrote: A VBA script has nothing to do with SAS You'l probably have more luck asking a purely VBA question on a Microsoft forum. With SAS' move away from DDE, posts asking for help with DDE lead to replies saying, "Don't use DDE, that's old technology, it's unsupported, and risky" and saying "use the SAS VBA add-in instead if you are trying to do things you used to do with DDE."   I think this forum is a fine place for people to ask for VBA help now that SAS has pushed VBA as the alternative to DDE. Would you tell posters in the past who asked about DDE, before it was put to pasture, to go to a Microsoft forum? I would hope not.   My recommendation would be to do a google search first as his question isn't SAS-specific but I wouldn't direct him away from posting on this forum.   Here's some code that may get him pointed in the right direction: (lifted gratuitously from stackoverflow:  https://stackoverflow.com/questions/6688131/test-or-check-if-sheet-exists)   Function CheckIfSheetExists(SheetName As String) As Boolean CheckIfSheetExists = False For Each WS In Worksheets If SheetName = WS.name Then CheckIfSheetExists = True Exit Function End If Next WS End Function     ... View more

@vikrantarora25 wrote: Hi,   Would be grateful if some expert on the forum can help me understand how to decide optimum number of leaves in a decision tree analysis. I am using SAS and if I supply leaves=6 in my model then miss-classification rates for validation & training data sets are 18.6% & 18.8% respectively. And SAS lists 5 variables which are significant.   And if I don't supply leaves count in the code and let SAS decide it, then SAS after pruning takes 10 as leaves count and miss-classification rates for validation & training data sets are 17.5% & 16.9% respectively. And SAS lists 6 variables which are significant. Now that the miss-classification rates have reduced & trees after pruning have increased from 4 to 10, is it a good thing or it indicates overfitting?   Looking forward to opinions of experts in this group.   Thanks & Regards Vikrant There's a subjectivity to model building. You need to consider the following questions:   1) Are the variables in a given model likely to be related to the outcome? If you are doing exploratory modeling then you may not have a good idea about this.   2) What is a large misclassification rate? It depends on what you are trying to do. What does being wrong 1/5th of the time mean for your use of the model? Is that an acceptable misclassification rate? No one can answer this for you. There may be some models where you can only put up with very small misclassification rates and others where the rates can be larger.   ... View more

I don't know of a way to have ODS embed excel formulas into an excel file.   As a workaround, you could use R and a package like openxlsx or xlconnect and SAS IML to add Excel functions to an excel spreadsheet.   To improve the functionality of creating excel files from within SAS, I create the data to be placed into an excel file using SAS and then call IML from within SAS to invoke R, pass the data to R and any commands associated with openxlsx or xlconnect to create and modify the excel file I want to create.   This paper provides details on how we do that: https://analytics.ncsu.edu/sesug/2017/SESUG2017_Paper-109_Final_PDF.pdf   ... View more

https://www.mwsug.org/proceedings/2010/excel_db/MWSUG-2010-166.pdf     ... View more