Hello,
I've currently created a SAS program that outputs an XLS file, that i later on open in Excel to apply a macro to it that removes empty lines.
Is there any way I could skip the step where I open it in Excel and directly execute the macro in SAS?
Note that I've looked for many hours and even asked a SAS consultant how I could do this directly in SAS but unfortunately I have to use this VBA macro.
Regards
Could you give an example of the output? Removing empty lines, at first glance, seems like a trivial operation in SAS.
Hey, thanks for your quick reply!
First of, here's the VBA code I need to run:
Sub DeleteAllEmptyRows()
Dim LastRowIndex As Integer
Dim RowIndex As Integer
Dim UsedRng As Range
Set UsedRng = ActiveSheet.UsedRange
LastRowIndex = UsedRng.Row - 1 + UsedRng.Rows.Count
Application.ScreenUpdating = False
For RowIndex = LastRowIndex To 1 Step -1
If Application.CountA(Rows(RowIndex)) = 0 Then
Rows(RowIndex).Delete
End If
Next RowIndex
Application.ScreenUpdating = True
End Sub
I've attached both current and wanted output files! Thanks
Please post the SAS code that creates the Excel spreadsheet.
here it is:
data age;
input ctr name $ age;
cards;
123 paul 52
123 peter 41
123 jack 16
123 caroline 12
;
run;
data height;
input ctr name $ height;
cards;
123 paul 185
123 peter 176
123 jack 172
123 caroline 168
;
run;
ODS EXCEL file = "path/to/file.xls";
ODS EXCEL options (sheet_name="data" sheet_interval="none" index="yes" flow="tables");
ODS SELECT ALL;
ODS TEXT='***AGE_OPEN';
PROC PRINT data=age noobs;
QUIT;
ODS TEXT='***AGE_CLOSE';
ODS TEXT='***HEIGHT_OPEN';
PROC PRINT data=height noobs;
QUIT;
ODS TEXT='***HEIGHT_CLOSE';
ODS EXCEL CLOSE;
This may be a very elaborate way around it, but I guess it's still quicker than having to run a VBA script after exporting. I would also like to see a solution which, perhaps, addresses the empty lines generated by PROC PRINT directly.
In the interim, however, import the created XLSX file into a SAS dataset, remove the empty lines, and export it again. Add this to the end of your existing code and it should remove the empty lines for you.
/* ===== Import the newly created XLSX file into SAS and remove blanks ===== */
%let excelfilename = %str(~/file.xlsx);
proc import datafile="&excelfilename." DBMS=xlsx out=temp1 replace;
getnames = no;
sheet = 'data';
run;
data temp2;
set temp1;
/*Loop over all the columns in a row, if a non-missing column exists*/
/*the row will not be deleted*/
array allnums _numeric_;
do over allnums;
if not missing(allnums) then contains_data = 1;
end;
array allchars _character_;
do over allchars;
if not missing(allchars) then contains_data = 1;
end;
/*Delete rows with all missing values*/
if not(contains_data) then delete;
drop contains_data;
run;
proc export data=temp2 dbms=xlsx outfile="&excelfilename." replace;
putnames = no;
sheet = 'data';
run;
Probably, otherwise just change your file extension to .xlsx and see what happens
You could try just switching DBMS=XLSX to DBMS=XLS (or DBMS=EXCEL depending on backwards compatibility) and then changing the file extension accordingly.
I was able to remove one empty line (between two text lines) by using an additional option:
ODS EXCEL options (sheet_name="data" sheet_interval="none" index="yes" flow="tables" suppress_bylines="yes");
but the empty lines created by each separate proc print remain. This seems to be an inherent "problem" with ODS EXCEL.
You can create a VBS file, automatically execute it from SAS and delete it afterwards:
%macro executeVBS(xmlFileName=%str(C:\Temp\Temp1),vbsFileName=%str(C:\Temp\classtest3.vbs));
data _null_;
file "&vbsFileName";
put " Set oXL = CreateObject(""Excel.Application"")";
put " Set FSO = CreateObject(""Scripting.FileSystemObject"")";
put " Dim objWorkBook";
put " oXL.Visible=False";
put " oXL.DisplayAlerts = False";
/*
...
*/
put " oXL.ActiveWorkbook.SaveAs ""&xmlFileName.""";
put " oXL.DisplayAlerts = True";
put " oXL.Quit";
put " oXL.ErrorCheckingOptions.NumberAsText = False";
put " Set oXL = Nothing";
put " FSO.DeleteFile(""&vbsFileName""), DeleteReadOnly";
put " Set FSO = Nothing";
put " Set objWorkBook = Nothing";
run;
data _null_;
call system("""&vbsFileName""");
run;
%mend executeVBS;
%executeVBS(xmlFileName=&createdFileName..xml,vbsFileName=&createdFileName.pivot.vbs);
- Cheers -
Enterprise Guide always runs on Windows. But the SAS code you submit via EG could be running on some other operating system. You probably need to make sure that the machine where SAS is running can run Excel.
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.