Hello jdk123, I apologize for this very late response.  We would encourage you to speak to a sales rep (1-800-727-0025) or request information from our sales group (http://www.sas.com/contact/formrouter.html)  because most of our forum responders cannot answer these questions.  Hope this helps.  ... View more

I believe you would need the SAS Add-In for Microsoft Office for that. It needs to be licensed. ... View more

I fyou have problem attending to a live class, there is an e-learning course that could be a good starting point. It still uses 9.1.3, but I don't think that OLAP Cube Studio has changed that much...? /Linus https://support.sas.com/edu/schedules.html?id=392&ctry=US ... View more

When building OLAP cubes, you could use a star schema as input, which often is preferred from a performance standpoint. There is no modelling tool in SAS. For modelling you could use tools like MS Visio or CA ERwin. Once created, they can be imported/generated in SAS. For populating the dimensional model/star schema, the most appropriate tool (if available) is Data Integration Studio. /Linus ... View more

in a data step I would define an array using the : (colon) suffix to include all the colXX variables and just accumulate the last 3 like Array cols(*) col: ; last3 = 0 ; do idx = dim(cols) to dim(cols)-2; last3+cols( idx); end; Then no matter how many COLxx columns are present only the last 3 are summed ... View more

It is possible. $w.d is only the cloth of variable ,in sas it is numeric. if you want different cloth for the same variable ,you can create another variable to contain the format then use function putn() or putc() to get it. [pre] _name_ 2001 2002 formate acct1 $500 $600 dollar8. acct2 $400 $300 dollar8. acct3 200 100 8. acct4 300 200 8. [/pre] Ksharp ... View more

I am confused .But my code work fine. If you also have problem ,can manually to format them format _: dollar10. ; [pre] data temp; input year (acct1 acct2 acct3 ) ( : dollar10.); format acct: dollar10.; datalines; 2001 $100.0 200 $300 2002 $400 500 $600 run; proc transpose data=temp out=tem ; id year; run; proc print noobs;run; _NAME_ _2001 _2002 acct1 $100 $400 acct2 $200 $500 acct3 $300 $600 [/pre] Ksharp ... View more

If we assume that means the variable with the largest value of VARNUM then... [pre] 42 proc sql; 43 select name into :lastName separated by ' ' 44 from dictionary.columns 45 where libname='SASHELP' and memname='CLASS' 46 having varnum eq max(varnum) 47 ; NOTE: The query requires remerging summary statistics back with the original data. 48 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.03 seconds cpu time 0.03 seconds 49 run; 50 %put NOTE: LastName=&lastname; NOTE: LastName=Weight [/pre] Use LASTNAME in a KEEP data set opion. ... View more

Thanks Oliver, you have been great help. ... View more

HI: First -- are you doing this in a Stored Process or using the SAS Add-in for Microsoft Office??? If you are NOT doing this in the context of a Stored Process then you should move or repost a new version of this question probably to the ODS and Base SAS Reporting forum (assuming you want to generate a report that you can open in Excel). If you are using the SAS Add-in for Microsoft Office and stored processes, then I would really suggest that you move your question to Tech Support. There seems to be some discrepancies between your original post and this post. In your original post, you said you had a CSV file. Now, in this post, you say you have an Excel file. Does this mean that you have 1 workbook with 2 worksheets (case1 and case2)?? Or do you have 2 separate workbooks -- a case1 workbook or a case 2 workbook??? An "Excel file" is not the same as a CSV file -- unless you are saving the Excel file as a CSV file and you are showing your "version" of what's in the Excel file. If you indeed have a CSV file, I would prefer to see that posted. As you will see, the answer to your questions really depend on accuracy of information about the input file and the type of output you want to create. Your questions: 1. what is a best way to read in the file to a dataset? it is a good practice to read in the titles for both case 1 and case2? -- If you have a CSV file, you can read the CSV file with either a DATA step program or with PROC IMPORT --If you have an Excel workbook, you can read the Excel workbook with either PROC IMPORT or the SAS Excel LIBNAME engine (or the ODBC engine or the OLE-DB engine) SAS expects your input data to be rectangular in structure -- rows and columns with no embedded graphs, for example. By default, if your Excel sheet has titles or formulas, SAS will not retain those in the SAS dataset. 2. how can i add in two additional columns for both case 1 and case2 and reproduce the output by using the same format as above; wih the final report how to have the user to view the formulas set in the original spreadsheet file? --As I explained above, once you read the data into a SAS dataset, there is no visibility of the formulas from the original workbook/worksheet. SAS could create the 2 additional columns you want in a variety of different ways. Once you have your data in a SAS dataset, you could use PROC SQL or a DATA step program to add the new columns to the data. If you want to add columns onto a report, then the best way to do that is with PROC REPORT. 3. is possible to add additional columns with group values to the output file. --I have a hard time understanding what you mean by addition COLUMNS, since what you used as an example looks like 3 columns under one header called 'MyValue'. I suspect that this issue is the least of your worries -- if you were generating a report, as I said, there are ways to use a COMPUTE block with PROC REPORT to generate extra columns and ways with PROC REPORT to generate extra summary rows. But until you resolve #1 and clarify what your input data is and how you will get it into SAS; and resolve #2 - -the issue with somehow retaining the Excel formulas -- #3 is almost irrelevant until #1 and #2 are solved. cynthia ... View more

I was having "SAS stored process API failed to load the stored process code. Index was outside the bounds of the array" problems and it was a trouble ticket to SAS Support. They ended up working on the JBOSS service. So you might want to open a ticket with SAS Support. Jim Baillie ... View more

Hi: I'm not sure I understand what you mean. You have an OLAP cube you want to open in Excel or you already have pivot tables in Excel that you are trying to read/import with SAS??? Or you want to produce pivot tables with SAS from SAS data??? Or you want to produce a series of reports, each of which represents one of the dimensions. Do you have SAS code that you are working on?? ODS code?? If so, what is your code like and since your ultimate desire is an Excel file, what ODS method are you using: ODS HTML, ODS HTML3, ODS CSV, ODS MSOFFICE2K, ODS MSOFFICE2K_X, ODS TAGSETS.EXCELXP, ODS TAGSETS.TABLEEDITOR??? All of those destinations will create files that Excel can open and render. Which one are you using? cynthia ... View more