Your SAS programs, embedded in web apps and elsewhere

SAS Automation

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

SAS Automation

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.

 

 

 


Accepted Solutions
Solution
‎08-18-2017 03:06 PM
SAS Employee
Posts: 3

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.

 

Hope this helps,

Tim Beese

View solution in original post


All Replies
Super User
Posts: 18,603

Re: SAS Automation

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.

 

 

 


 

Occasional Contributor
Posts: 7

Re: SAS Automation

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.

SAS Employee
Posts: 295

Re: SAS Automation

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

 

Occasional Contributor
Posts: 7

Re: SAS Automation

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

Super User
Posts: 18,603

Re: SAS Automation

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. 

Solution
‎08-18-2017 03:06 PM
SAS Employee
Posts: 3

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.

 

Hope this helps,

Tim Beese

Occasional Contributor
Posts: 7

Re: SAS Automation

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

SAS Employee
Posts: 3

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.

 

ShowRowNumbers.PNG

 

Tim Beese

Occasional Contributor
Posts: 7

Re: SAS Automation

Thank you very much!!! Got it!

 

Thanks,

Subbu.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 330 views
  • 5 likes
  • 4 in conversation