BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DLROW
Quartz | Level 8

Capture.PNG

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.

Thanks in advance.

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
TimBeese
SAS Employee

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.

 

Hope this helps,

Tim Beese

View solution in original post

9 REPLIES 9
Reeza
Super User

You need to provide more information. 

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:

Capture.PNG

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.

Thanks in advance.

 

 

 


 

DLROW
Quartz | Level 8

PROC PRINT DATA=WORK.SORTTempTableSorted
NOOBS
LABEL
;
VAR "GROUP NAME"n "GROUP NUMBER"n "BILLING PROVIDER ID"n "BILLING PROVIDER LAST NAME"n "BILLING PROVIDER TAX ID"n "PAR SERVICE IND"n "BILLING PROVIDER STREET ADDR1"n "BILLING PROVIDER STREET ADDR2"n "BILLING PROVIDER CITY"n "BILLING PROVIDER STATE"n
"BILLING PROVIDER ZIP CODE"n "BILLING PROVIDER COUNTY NAME"n "UTILIZING MEMBERS"n ADMITS DAYS "PLAN PAID"n;
RUN;

 

Thank you for your reply.

 

Yes, Proc Print with no OBS option is used.

Vince_SAS
Rhodochrosite | Level 12

Based on the code that you provided, it seems that your VBA code is responsible for creating the row numbers.

 

Vince DelGobbo

SAS R&D

 

DLROW
Quartz | Level 8

I am using the following VBA code

 

Sub Click()
Dim SAS As Object
Set SAS = Application.COMAddIns.Item("SAS.ExcelAddIn").Object
SAS.Modify ("GR_AR_BillPrvdRpts")
SAS.Refresh ("SASApp_SASDATA_FACILITY_BILL")
SAS.Refresh ("SASApp_SASDATA_PROFFESSIONAL_BILL")
SAS.Refresh ("SASApp_SASDATA_PROVIDER_PROMPTS")
MsgBox "The reports have been refreshed", vbInformation
End Sub

Reeza
Super User

Run the code in EG, does it generate the results you want, without the row numbers?

I would suggest you talk to SAS tech support on this question, we don't have enough information to help you out, we'd pretty much need to see the full process. 

TimBeese
SAS Employee

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.

 

Hope this helps,

Tim Beese

DLROW
Quartz | Level 8

Thanks a lot, it worked. I unchecked the "show record numbers" in an Excel.

 

In global option, 

I couldn't find the show record numbers, below is the snapshot,

 

Capture.PNGCapture.PNG

TimBeese
SAS Employee

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.

 

ShowRowNumbers.PNG

 

Tim Beese

DLROW
Quartz | Level 8

Thank you very much!!! Got it!

 

Thanks,

Subbu.

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
  • 9 replies
  • 2774 views
  • 5 likes
  • 4 in conversation