Hi folks,   I have this SUM function that is giving me the error message:  ERROR: Summary functions nested in this way are not supported. The block of PROC SQL code that is giving me trouble looks like this: SUM(CASE WHEN 'AoE'n = "BAG" THEN (GN.Principal * CALCULATED 'DC Term'n) ELSE (GN.Principal * GN.'FAC Term'n) END) AS 'FTP LP Y'n When I execute the code without the SUM(), it works and shows all values in the column (calculated from other values). What I need to do is SUM all the values in that column into a singular cell....What is odd to me is that when I used SUM() here, it does work, which I am using in an earlier portion of my PROC SQL code and it looks like this: SUM(CASE WHEN 'FAC Type'n = "DC" THEN (GN.Principal * GN.'FAC Term'n) ELSE 0 END) / SUM(CASE WHEN 'FAC Type'n = "DC" THEN GN.Principal ELSE 0 END) AS 'DC Term'n, I can't seem to figure out the differences in syntax between the two blocks of code. Does anyone have any ideas why I might be getting this error? ... View more

Hi folks,   I'm newer to SAS and basically I'm looking to automate some Excel tasks. The current process looks like this: Data team sends us a data extract via .xlsx (they auto-drop it into the same Google Drive shared folder everyday at a specific time) We copy and paste the data from that .xlsx workbook to an "intermediary workbook", which contain various Excel formulae that perform further calculations on the data and structures it in the right way for SAP to easily ingest (next step).  We take the output of the intermediary workbook and paste it into the final macro-enabled workbook. This workbook has macros which create a .csv copy of the data we pasted in  initially and drop it to a specific filepath location on our Shared Network Drive, which SAP automatically ingests as an end of day process, which updates the respective data in SAP. What I would like to do is: Use SAS to ingest the initial .xlsx data file Replace the "intermediary" workbook by replicating the Excel formulaic calculations in SAS Replace the final macro-enabled workbook by pushing the .csv output file to a specific Windows S: Drive file location. My only challenge right now is that I'm essentially using SAS Enterprise Guide for a task that isn't a super common use-case. The intention however is to reduce human error that comes with copy/paste activities, as well as streamline the process into one comprehensive SAS program with an audit trail of what the program is doing each day rather than leave it up to a spreadsheet tool to do it. What are your guys thoughts as to how feasible this is and how would I get started at a high level to accomplish this?   What I figured would be a good first step is to simply grab the .xlsx file from Google Drive, then paste it into the existing "Intermediary Workbook" and then pick up the output and paste it into the "final macro workbook", basically replacing only the human steps. The next step after that would be to try to tackle the actual Excel workbooks themselves and see if we can replicate the functionality of that intermediary workbook. ... View more