Write and execute VBA macro in SAS EG

[polpel]

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

[jvdl]

Could you give an example of the output? Removing empty lines, at first glance, seems like a trivial operation in SAS.

[polpel]

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

[Kurt_Bremser]

Please post the SAS code that creates the Excel spreadsheet.

[polpel]

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;

[jvdl]

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;

[polpel]

hey man, thanks I'll try that. Quick question: would this work with a .xls file?

[jvdl]

Probably, otherwise just change your file extension to .xlsx and see what happens

[polpel]

I mean a .xls file in the proc export
I made an error in the code I sent, it's supposed to be path/to/file.xls

[jvdl]

 You could try just switching DBMS=XLSX to DBMS=XLS (or DBMS=EXCEL depending on backwards compatibility) and then changing the file extension accordingly.

[Kurt_Bremser]

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.

[Oligolas]

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);

[polpel]

This works for an .xls file?

[Tom]

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.