vba - pass sas macro variables to VB script as parameters -
below sas code used forecasting , create excel output, once output stored in excel file, call vba macro generate graph each excel file , each sheet of work book.
in sas macro have defined output path excel files , want same path used in vba macro without making fixed value, if run sas code output @ different location vba picks path sas macro variable.
/* sas code below */ %macro forcaseting(lib,dsn); options fmtsearch=(sasuser work); proc sql noprint; select distinct name :var_name separated '|' dictionary.columns upcase(libname) = "%upcase(&lib.)" , upcase(memname) = "%upcase(&dsn)" , upcase(name) ne 'month' , upcase(type) = 'num' ; quit; %put &var_name; proc datasets lib=work nolist kill;run; proc format; picture mny low - high = '%b-%y' (datatype=date); run; %put &var_name.; %let = 1; %do %while (%scan(&var_name.,&i,%str(|)) ne ); %let cur_var = %scan(&var_name.,&i,%str(|)); %put &cur_var.; data %sysfunc(compress(a_&cur_var.,,kad)); set &lib..&dsn.(keep= month &cur_var.); retain n 0; if not missing(&cur_var.) , (&cur_var. gt 0) n +1; call symputx ("n",n,'l'); run; %if %sysevalf(&n.) gt 5 %then %do; /*forecasting using hpf*/ proc hpf data=%sysfunc(compress(a_&cur_var.,,kad)) outfor=%sysfunc(compress(a_&cur_var._for,,kad)) outstat=%sysfunc(compress(a_&cur_var._stat,,kad)) lead=4; id month interval=month; forecast &cur_var./ model=bestall criterion=mape; run; data _forecast; length deal_name $ 60.; set %sysfunc(compress(a_&cur_var._for,,kad)); deal_name = "&cur_var."; if actual not in (0 .) mape = abs((actual-predict)/actual); else mape=.; format mape percent8.2; run; data _final_forecast (drop=_:) ; length deal_name $ 60.; set %if %sysfunc(exist(_final_forecast)) %then %do; _final_forecast %end; /* %sysfunc(compress(a_&cur_var._for,,kad));*/ _forecast ; run; options nomprint nomlogic; ; /*forecasting using arima*/ proc arima data=%sysfunc(compress(a_&cur_var.,,kad)); identify var=&cur_var. ; estimate p=1 q=1 ;/*input=per_bl_acs */; run; forecast lead=4 id=month interval=month out=%sysfunc(compress(a_&cur_var._arima,,kad)); run; quit; /*get observation count of above dataset */ %let dsid=%sysfunc(open(%sysfunc(compress(a_&cur_var._arima,,kad)))); %let num=%sysfunc(attrn(&dsid.,nlobs)); %let rc=%sysfunc(close(&dsid)); %if %eval(&num.) gt 1 %then %do; ods tagsets.excelxp file="c:\data\sasoutput\bpo\output_proc_arima\&cur_var..xls" style=normal options ( sheet_label=' ' sheet_name="&cur_var." suppress_bylines='yes' autofit_height='yes' autofilter='all' frozen_headers='1' orientation = 'landscape' ); data %sysfunc(compress(a_&cur_var._f,,kad)); set %sysfunc(compress(a_&cur_var._arima,,kad)); if &cur_var not in (. 0) mape = abs((&cur_var-forecast)/&cur_var.); else mape=.; format mape percent8.2; run; proc print noobs;run; ods tagsets.excelxp close; ; %end; %end; %let = %eval(&i.+1); %end; ods tagsets.excelxp file="c:\data\sasoutput\bpo\output_proc_hpf\hpf.xls" style=normal options ( sheet_interval='bygroup' sheet_label=' ' suppress_bylines='yes' autofit_height='yes' autofilter='all' frozen_headers='1' orientation = 'landscape' ); proc sort data=_final_forecast; deal_name; run; proc print data=_final_forecast noobs ; deal_name; run; ods tagsets.excelxp close; ; /*create graph each of above file using 2 vba codes */ /*intiate excel */ options noxwait noxsync; data _null_; rc=system('start excel'); rc=sleep(5); run; /*call vba macro create graph each excel file , each sheet*/ filename sas2xl dde 'excel|system'; data _null_; file sas2xl; put "[open(""c:\vbatrustedlocation\arima_template.xlsm"", 0 , true)]"; put "[run(""create_arima_chart"")]"; put "[run(""create_hpf_chart"")]"; *put '[save.as("c:\data\sasoutput\bpo\output_proc_arima\acosta1.xls")]'; put '[file.close(false)]'; put '[quit()]'; run; %mend forcaseting; /* vb macro code below */ sub create_arima_chart() dim strfile string dim cell range, strtemp string, c variant dim sh worksheet dim integer application.screenupdating = false application.calculation = xlcalculationmanual strfile = dir("c:\data\sasoutput\bpo\output_proc_arima\*.xls") ' looks each file csv extension while len(strfile) > 0 ' while file name greater nothing workbooks.open filename:="c:\data\sasoutput\bpo\output_proc_arima\" & strfile ' open current workbook each sh in thisworkbook.worksheets = 1 worksheets.count worksheets(i).activate each cell in intersect(range("a1:h30"), activesheet.usedrange) strtemp = cell.value each c in array("xz") strtemp = strtemp & range(c & cell.row).value next c if trim(strtemp) = "." cell.clearcontents end if next cell columns("a:h").columnwidth = 9.57 activesheet.shapes.addchart.select ' add chart activechart.charttype = xlline ' add chart type activechart.setsourcedata source:=range("$a1:$c1", range("$a1:$c1").end(xldown)) ' set source range used cells in a:b on open worksheet activechart.parent .height = .height * 1 'increase height 50% .width = .width * 1.2 'increase width 50% .top = 20 .left = 450 end activechart .legend.select selection.position = xlbottom .axes(xlvalue).majorgridlines.select selection.delete end 'note setting of source work while there no skipped blank if 'have empty rows in source data please tell me , can provide ' way information next next sh application.displayalerts = false activeworkbook.saveas filename:="c:\data\sasoutput\bpo\output_proc_arima\output\" & strfile, _ fileformat:=xlopenxmlworkbook, createbackup:=false ' save file excel xlsx current files name activeworkbook.close ' close when finished before opening next file can removed if you'd keep open review @ end of loop. application.displayalerts = true application.calculation = xlcalculationautomatic application.screenupdating = true strfile = dir ' next file in dir loop end sub sub create_hpf_chart() dim strfile string dim cell range, strtemp string, c variant dim sh worksheet dim integer application.screenupdating = false application.calculation = xlcalculationmanual strfile = dir("c:\data\sasoutput\bpo\output_proc_hpf\*.xls") ' looks each file csv extension while len(strfile) > 0 ' while file name greater nothing workbooks.open filename:="c:\data\sasoutput\bpo\output_proc_hpf\" & strfile ' open current workbook each sh in thisworkbook.worksheets = 1 worksheets.count worksheets(i).activate each cell in intersect(range("a1:h30"), activesheet.usedrange) strtemp = cell.value each c in array("xz") strtemp = strtemp & range(c & cell.row).value next c if trim(strtemp) = "." cell.clearcontents end if next cell columns("a:h").columnwidth = 9.57 activesheet.shapes.addchart.select ' add chart activechart.charttype = xlline ' add chart type activechart.setsourcedata source:=range("$a1:$c1", range("$a1:$c1").end(xldown)) ' set source range used cells in a:b on open worksheet activechart.parent .height = .height * 1 'increase height 50% .width = .width * 1.2 'increase width 50% .top = 20 .left = 450 end activechart .legend.select selection.position = xlbottom .axes(xlvalue).majorgridlines.select selection.delete end 'note setting of source work while there no skipped blank if 'have empty rows in source data please tell me , can provide ' way information next next sh application.displayalerts = false activeworkbook.saveas filename:="c:\data\sasoutput\bpo\output_proc_arima\output\" & strfile, _ fileformat:=xlopenxmlworkbook, createbackup:=false ' save file excel xlsx current files name activeworkbook.close ' close when finished before opening next file can removed if you'd keep open review @ end of loop. application.displayalerts = true application.calculation = xlcalculationautomatic application.screenupdating = true strfile = dir ' next file in dir loop end sub /*%forcaseting(bpo,attrition_monthwise_may_forecast);*/
i had same issue creating dos script, , solution came with, may applicable here, create script parameters coded script , call script itself.
hope helps.
Comments
Post a Comment