BookmarkSubscribeRSS Feed
polpel
Fluorite | Level 6

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

13 REPLIES 13
jvdl
Obsidian | Level 7

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

polpel
Fluorite | Level 6

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

polpel
Fluorite | Level 6

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
Obsidian | Level 7

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
Fluorite | Level 6
hey man, thanks I'll try that. Quick question: would this work with a .xls file?
jvdl
Obsidian | Level 7

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

polpel
Fluorite | Level 6
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
Obsidian | Level 7

 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
Super User

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
Barite | Level 11

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 -

polpel
Fluorite | Level 6
This works for an .xls file?
Tom
Super User Tom
Super User

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.

SAS Innovate 2025: Call for Content

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!

Submit your idea!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 3574 views
  • 1 like
  • 5 in conversation