Hi all,
I have the following SAS code which opens a specific file (PEOPLE_TEST.xml) and converts it to XLS.
However, the macro variables &path and &filename would not resolve when I open the vb script (PLIST.vbs).
I would like to know how can the SAS macro variables resolve when the vb script is made. Thank you!
/* Here is the SAS code: */
%let path = C:\Users\angelique.castaneda\Documents\Work\98_SAS References;
%let filename = PEOPLE_TEST;
%macro VB;
DATA _NULL_;
FILE "&path.\PLIST.vbs";
PUT 'Set objExcel = CreateObject("Excel.Application")';
PUT 'objExcel.Visible = FALSE';
PUT 'objExcel.DisplayAlerts = FALSE';
PUT 'Set objWorkbook = objExcel.Workbooks.Open(""&path".\&filename..xml")';
PUT 'objExcel.ActiveWorkbook.SaveAs"&path.\&filename..xls",51';
PUT 'objExcel.ActiveWorkbook.Close';
PUT 'objExcel.Quit';
RUN;
%mend;
%VB;
The two lines with macro variables are enclosed in single quotes, while macro variable won't resolve unless unquoted or ONLY surrounded by double quotes.
There is a way to do that directly using multiple sets of double quotes, or using something like:
PUT 'Set objWorkbook = objExcel.Workbooks.Open("'@;
PUT "&path.\&filename..xml"@;
PUT '")';
The two lines with macro variables are enclosed in single quotes, while macro variable won't resolve unless unquoted or ONLY surrounded by double quotes.
There is a way to do that directly using multiple sets of double quotes, or using something like:
PUT 'Set objWorkbook = objExcel.Workbooks.Open("'@;
PUT "&path.\&filename..xml"@;
PUT '")';
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.