SAS Automation

I tried to run the automated SAS report in Excel by using VBA script. I am getting the row number in excel. How to remove that row number? I tried deleting that column and saved it. However, when a different report is run, I am receiving that row number.

Re: SAS Automation

Since you're using VBA to refresh results that were created by the Add-In for Microsoft Office, it is the SAS Add-In that is putting your row numbers back in. The reason that the row numbers are there is that when you originally opened the data, you must have had the checkbox for "Insert record numbers in the first column" selected. Once you do that, the Add-In will put the record numbers there, and will put them back each time that you refresh.

To make this stop, place your selection within the table that you want to change, and go to the SAS tab on the ribbon. Select Properties. This will bring up the properties for this content. On the Data tab of the Properties dialog there will be a checkbox for "Show record numbers". Uncheck that and click OK. Now when you refresh the table, the record numbers should not be rendered.

You can change your global option, SAS->Tools->Options ->Data->Show Record Numbers, so that future datasets you open will not have the row numbers. But, this does not affect tables that have already been rendered in the document. Once there, the only way to change their behavior is through the properties dialog.

Re: SAS Automation

Basically, figure out what's causing the number to appear and remove it.

My guess - you're using PROC PRINT and need the NOOBS option, but you haven't shown the code or what you're doing so it's an educated guess. PLEASE post more details in your questions.

Subbu1 wrote:

Hi ,

I tried to run the automated SAS report in Excel by using VBA script. I am getting the row number in excel. How to remove that row number? I tried deleting that column and saved it. However, when a different report is run, I am receiving that row number.

Re: SAS Automation

Since you're using VBA to refresh results that were created by the Add-In for Microsoft Office, it is the SAS Add-In that is putting your row numbers back in. The reason that the row numbers are there is that when you originally opened the data, you must have had the checkbox for "Insert record numbers in the first column" selected. Once you do that, the Add-In will put the record numbers there, and will put them back each time that you refresh.

To make this stop, place your selection within the table that you want to change, and go to the SAS tab on the ribbon. Select Properties. This will bring up the properties for this content. On the Data tab of the Properties dialog there will be a checkbox for "Show record numbers". Uncheck that and click OK. Now when you refresh the table, the record numbers should not be rendered.

You can change your global option, SAS->Tools->Options ->Data->Show Record Numbers, so that future datasets you open will not have the row numbers. But, this does not affect tables that have already been rendered in the document. Once there, the only way to change their behavior is through the properties dialog.

Re: SAS Automation

You're looking at the options for SAS Enterprise Guide. The option you need to check is the one with the SAS Add-In for Microsoft Office.

I do need to correct how you get to this option, it is no longer on the main Options dialog. We present this option when you choose which data to open. Here are the steps:

1. From Excel, go to the SAS tab. Choose SAS Data on the ribbon.

2. This brings up the View SAS Data dialog. You can choose your data here, as well as where you want to view it. On the right side of this dialog we have some options that pertain to how you want to view the data.

3. Find the "Insert record numbers in the first column" option, and uncheck it. Doing this will cause your data to be displayed without the row numbers.