Hey guys,   I have been looking around but I could not find this information, maybe you could help me clarify this ...   I have been using SAS either installed on my local machine, Windows based (usually for small developments) and on the server for deploying the solutions.    How does SAS Enterprise Guide submits the code to the SAS environment to be executed? Is there a executable that does the actual running? Where would this be located usually? Or is SAS running as a service?   Thanks 🙂 ... View more

Hi all,   I have the following situation.   I have some huge underlying data set - imagine it like orders.   RowID          Product          Subproduct          Counterparty          Amount 1                   A                    M                          X                            10 2                   A                    M                          Y                             5 3                   B                    M                          Z                             7 .... and so on.   I need to generate a report something like this:   Category          Amount 1                      amount1 (is a sum of category 1.1 and 1.2) 1.1                   amount1_1 (is a sum of category 1.1.1 and 1.1.2) 1.1.1                amount1_1_1 1.1.2                amount1_1_2 1.2                   amount1_2 (equals amount from underlying category 1.2.1) 1.2.1                amount1_2_1     I read in the specs of the report from an XLS where I specify the where conditions for the lowest level conditions (meaning the Categories 1.1.1 and 1.1.2) and for the higher level conditions the category IDs of the sub-sums. For example, for category 1.1.1. i read in "Product in (A,B) and Counterparty not in (Y,Z) and for category 1.1 i read in that is a sum of 1.1.1 and 1.1.2.    The XLS is something like this:   Category             Type             Content Category 1          Sum             Category 1.1, Category 1.2 Category 1.1       Sum             Category 1.1.1, Category 1.1.2 Category 1.1.1    Formula       Product = A and Subproduct = X Category 1.1.2    Formula       Product in (A,B) and Counterparty in (X,Z) Category 1.2       Sum             Category 1.2.1 Category 1.2.1    Forumula      Product = B .... and so on   I use a macro to go through the underlying data set and assign on a separate column the lowest level category where in the specs table the type is Formula (e.g 1.1.1 and 1.1.2). In case a row is assigned to more categories, I duplicate it and output it with each subcategory.   The output of the macro would be in a data set like this   RowID          Category 1                  1.1.1 1                  1.1.2 2                  No category 3                  1.1.2 3                  1.2.1   The report i need to produce should be at the end    Category                Amount Category 1             34 Category 1.1          27 Category 1.1.1       10 Category 1.1.2       17 Category 1.2          7 Category 1.2.1       7   Now my question - what would be the most efficient way of processing all the sums-ups and creatng the report I need considering that the underlying data set is quite huge and the report specification (the number of categories) is also quite big. I don't want to do this hardcoded, I want to be driven by the XLS read-in.   The most stupid way I could think about is to update the macro so that whenever I find a subcategory and I output the row (e.g. 1.1.1), I also read in which upper categories the row is included (in this particular case 1.1) and ooutput the row again, then repeat and output the row also for 1. This will surely increase the volume of data but then would be easier to just join these two data sets and sum the amounts group by category ID.    Do you have any other ideas?   Thanks 🙂 ... View more

Hi all,   Is it possible while reading in some CSVs in a data step with infile, to perform also some kind of lookup mapping against some other tables which were already previously created to calculate some aditional columns on the fly?   For example, let's assume I read in a CSV which contains two columns, entity and currency. I need to calculate a 3rd column, reporting_currency which is obtained by looking up the entity-currency tuple in another table I already have imported.   Thank you for the help! ... View more

Hi all, I have processed a lot of data in SAS and I need to put out a lot of XLS files for our colleagues who analyze the results. There are about 100 XLS files, each with 30 sheets ...   In each XLS, there are about 5 different sheet types (each sheet type fed by a different SQL) and each XLS contains all sheet types (queries ran for different currencies).   I am using SAS 9.4.   Do you have any good sugestions how to do the XLS writing? Should I use proc export or some other way might be better?   Thanks a lot! ... View more