topic Write and execute VBA macro in SAS EG in SAS Programming

Write and execute VBA macro in SAS EG

polpel — Thu, 21 Nov 2019 09:29:04 GMT

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

Re: Write and execute VBA macro in SAS EG

jvdl — Thu, 21 Nov 2019 09:32:56 GMT

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

Re: Write and execute VBA macro in SAS EG

polpel — Thu, 21 Nov 2019 09:38:30 GMT

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

Re: Write and execute VBA macro in SAS EG

Kurt_Bremser — Thu, 21 Nov 2019 10:10:55 GMT

Please post the SAS code that creates the Excel spreadsheet.

Re: Write and execute VBA macro in SAS EG

polpel — Thu, 21 Nov 2019 10:19:04 GMT

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;

Re: Write and execute VBA macro in SAS EG

jvdl — Thu, 21 Nov 2019 12:56:22 GMT

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;

Re: Write and execute VBA macro in SAS EG

Kurt_Bremser — Thu, 21 Nov 2019 12:59:50 GMT

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.

Re: Write and execute VBA macro in SAS EG

polpel — Thu, 21 Nov 2019 13:22:07 GMT

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

Re: Write and execute VBA macro in SAS EG

jvdl — Thu, 21 Nov 2019 13:22:51 GMT

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

Re: Write and execute VBA macro in SAS EG

polpel — Thu, 21 Nov 2019 13:50:05 GMT

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

Re: Write and execute VBA macro in SAS EG

jvdl — Thu, 21 Nov 2019 14:07:55 GMT

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

Re: Write and execute VBA macro in SAS EG

Oligolas — Thu, 21 Nov 2019 14:17:30 GMT

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

Re: Write and execute VBA macro in SAS EG

polpel — Thu, 21 Nov 2019 15:02:42 GMT

This works for an .xls file?

Re: Write and execute VBA macro in SAS EG

Tom — Thu, 21 Nov 2019 15:05:53 GMT

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.