About Santt0sh

Santt0sh · ‎04-11-2022

Hi Kurt, Thank you for your quick response! Yes, you were correct, I missed putting xxx_xxxx_201001 in quotations. Data _NULL_; Month = intnx('month', Today(), -13); Put Month monyy7.; Run; data xxx; fle = 'xxx_xxxx_2020'; /*filename*/ a = scan(fle,-1,'_'); a1=(input(scan(fle,-1,'_'),best12.)); a2=(input(scan(fle,-1,'_'),6.)); a3=(put(scan(fle,-1,'_'),monyy7.)); format a1 monyy7. a2 monyy7. ; run;

Santt0sh · ‎04-11-2022

Hi All, I missed to complete the scan in the previous post please find the completed and working Data Month; Mnth = xxx_xxxxx_2010; Dte = scan(‘month’,-1,’_’); Dte1=(input(scan(‘Month’,-1,’_’),Best12.); Run;

Santt0sh · ‎04-11-2022

Hi All, I am trying to convert a char date value to MONyy7. I am not able to get the desired output. Data _NULL_; Month = intnx(‘month’, Today(), -13); Put monyy7.; Run; Output ::::: MAR2021 I am reading file names from a dataset. For example: Data Month; Mnth = xxx_xxxxx_2010; Dte = scan(‘month’,-1,’_’); Dte1=(input(scan(‘Month’,Best12.); Run; I’m not able to apply any of the date informats tried Anydttdtm and anydttdte Kindly suggest

Santt0sh · ‎04-07-2022

Hi Tom, Thank you for your suggestion!. It worked for me, i had make few changes to sas code used for calculations. Regards

Santt0sh · ‎04-05-2022

Hi, Yes I was expecting to create a new sheet as in Proc export. I don’t want to calculate the Tukey Fence manually as this will be a Monthly job in future. Thanks

Santt0sh · ‎04-05-2022

Hi Tom, Yes I understand what you are suggesting. I am not dumping all the data to the excel I am only creating a report using Proc report and trying to output the report as it is to Excel sheet. I’m not able to write the report to different sheets. The ods excel is replace the sheet. Kindly suggest Thanks

Santt0sh · ‎04-05-2022

Hi Tom, Thank you for your response. Let me try to explain: I have datasets for the last one year and I am trying to create table for corporate and no corporates and calculate as per the requirements for both the sections. Even if create the report as suggested by you for each moth by using where clause. Again I need to consolidate the report for the last 12 months. So I’m splitting the datasets and trying to create the report. Let me try as you have suggested. Thanks

Santt0sh · ‎04-05-2022

Hi, Thank you for your response!! I tried your suggestion but I have a huge data set h nice have split the table into Corporate and Non Corporates And I’m using a macro to do the calculations, and generate reports for each separately. If I keep sorting the dataset it might take long and the calculations also are different for each departments. I’m trying to export the report generated by the Proc report to an excel sheet : sales 2020.xlsx which will have data for sales for each month for corporate and non corporates. The calculations will be done in the SAS datasets. Kindly suggest

Santt0sh · ‎04-05-2022

I have used ODS TAGSETS.EXCELXP FILE="path " Style= analysis; ODS TAGSETS.EXCELXP FILE options (Sheet_Name = "CORPORATES" AUTOFILTER ='YES'); Proc Report data = SaleJan20 ; STAEMENTS -------; RUN; ODS TAGSETS.EXCELXP CLOSE; ODS LISTING; );

Santt0sh · ‎04-05-2022

Hi All, I am trying to create a SALES report using Proc Report for two categories of customers as of now (Corporates & Non-Corporates). I am able to create the Datasets with all the calculations using Proc Reports, but now I need to Export this Proc Report to EXCEL(.XLSX) in two different sheets Corporates and Non-Corporates. I am aware that this can be done using Proc Export and writing the data into two different Sheets in the Same Excel. I'm trying to Keep the Format and the report as it's in Excel, and the Footnote of the report Should display the Tukey Fences. I have calculated the Upper Bound and Lower Bounds using proc Univarate and have created macro variables for the Upper Bounds and Lower Bounds. Something like this: Proc Univariate data = Sale012020 outtable = SaleJan20 Noprint; Var Sales_Amount; Run; /*UPPER NAD LOWER BND CALCULATIONs*/ DATA SaleJan20 ; Set SaleJan20 ; lower = _Q1_ - (1.5*_QRANGE_); /*_QRANGE_ = Interquartile Range*/ UPPER= _Q3_+ (1.5*_QRANGE_) /*_QRANGE_ = Interquartile Range*/ Run; Data _NULL_; Set SaleJan20 ; Call Symput('UPPER',UPPER); CALL SYMPUT('LOWER',LOWER); RUN; The Format for the UPPER and LOWER needs to percent8.2 When I tried to apply the Percent to the Macro Variables on the Report it is Displayed as 1.6E %(For E.g.) I have tried using the ODS Exceltagsets and ODS excel File = options(); I am able to write the Output for (for e.g. Corporates ) to a sheet in the excel workbook, but not able to retain the formats and write the output of ( Non-Corporates) in another sheet. Could you please guide or Suggest this? Please find the attached Sales input and report in excel attached. Kindly note that I have created the Tukey Fences using Excel formulas and the Sales Amounts or Sale numbers are fictitious. Regards,

Santt0sh · ‎04-01-2022

Hi Kurt, Thank you for your quick response. 1. I would like to read anything or something from the shared point. 2. Currently I am manually downloading the file created by Tableau from Share Point and reading them into SAS by Proc Import and perform calculations and a generate report. Kindly Suggest me on #1.

Santt0sh · ‎03-31-2022

Hi All, I was trying to create summary of a report which is created on tableau in excel. To get the excel im current downloading the file and then importing the excel file into SAs and after minor changes to the SAs code I am running a summary statistics using Proc summary and then create another report and share it. I have never worked with Tableau, so wanted to know if I can read the excel file from Tableau and generate the required reports. The tableau reports are stored at a Share point. I tried accessing the files using filename statements but I keep getting a not found error. Can anyone suggest if I need to add any connections into order to aceesss the share point. Thank you

Santt0sh · ‎03-31-2022

Hi Kurt, Thank you for your Post!! Using the below mentioned code I was able to read the Excel files with headers on Multiple rows, and I was working on renaming the columns as the excel sheet keeps growing and also calculating the sales percentages for all the years. Regards,

Santt0sh · ‎03-29-2022

Hi Kurt, Thank you for your quick response! I was not able to upload the file to the existing post. Kindly find the attachment in the below post. https://communities.sas.com/t5/SAS-Programming/To-import-an-Excel-file-into-SAS-with-column-headers-on-more/m-p/804730#U804730 Thank you for your time and guidance!!

Santt0sh · ‎03-29-2022

Hi All, Please accept my Sincere Apologies for posting the same question again. I missed the attachment in my previous post and I'm not sure how to add an attachment to the existing post (https://communities.sas.com/t5/SAS-Programming/Import-an-excel-sheet-which-has-the-column-header-on-two-rows/m-p/804672/emcs_t/S2h8ZW1haWx8dG9waWNfc3Vic2NyaXB0aW9ufEwxQlFPV0dXOEkyQURCfDgwNDY3MnxTVUJTQ1JJUFRJT05TfGhL#M316904). I'm trying to read an excel file into SAS which has headers on two rows, I tried using the solutions provided here but I was not able to get the desired output. I thought of reading the .xlsx file twice and assigning the variable names to the merged columns in excel but later realized that the excel sheet/dataset keeps growing for the upcoming months or years. I also tried to transpose after importing the .xlsx file with GETNAME = NO OPTION. The transpose results. Variable Name Variable Type C 2 E 2 F 2 G 2 GRAND TOTAL 1 H 2 I 2 J 2 K 2 L 2 M 2 N 2 O 2 PROGRAM_TYPE 2 PRODUCT_TYPE 2 R 2 S 2 T 2 U 2 V 2 W 2 X 2 Y 2 _2020 2 _2021 2 Please find the excel attached, Kindly Suggest.

Online Status	Offline
Date Last Visited	‎07-22-2025 10:17 AM

Re: i am trying to add '-'between date9.

Re: i am trying to add '-'between date9.

Re: i am trying to add '-'between date9.

i am trying to add '-'between date9.

Re: Need Help running running oracle query usng SAS Pass thru or Libna...

Re: Need Help running running oracle query usng SAS Pass thru or Libna...

Need Help running running oracle query usng SAS Pass thru or Libname f...

Re: SAS AML Custom Scenario Coding

Re: Can we write multiple lines in an excel sheet using Proc report?

Re: Can we write multiple lines in an excel sheet using Proc report?

Understanding Attributes of Kubernetes Persistent Volumes

Re: Need Help running running oracle query usng SAS Pass thru or Libna...

Re: Run schedule now to PPM is success but exited on LSF

Re: Run schedule now to PPM is success but exited on LSF

Re: How to split a table/dataset

Re: create a macro variable uisng proc sql in a pass thru facility rs...

Re: Teradata Spool Space error

to export a Proc Report export to excel with formats and into differe...

How do I import files(.xlsx) files from Tableau

To import an Excel file into SAS with column headers on more than one ...

Re: Convert date 201001 to JAN2010

Re: Convert date 201001 to JAN2010

Convert date 201001 to JAN2010

Re: to export a Proc Report export to excel with formats and into dif...

Re: to export a Proc Report export to excel with formats and into dif...

Re: to export a Proc Report export to excel with formats and into dif...

Re: to export a Proc Report export to excel with formats and into dif...

Re: to export a Proc Report export to excel with formats and into dif...

Re: to export a Proc Report export to excel with formats and into dif...

to export a Proc Report export to excel with formats and into differe...

Re: How do I import files(.xlsx) files from Tableau

How do I import files(.xlsx) files from Tableau

Re: To import an Excel file into SAS with column headers on more than ...

Re: Import an excel sheet which has the column header on two rows

To import an Excel file into SAS with column headers on more than one ...