I get a excel workbook with 15 tabs from another department in our company.  The header rows for tabs 2-14 are linked to the header row in tab 1.  This way, if a column name is changed, the person only has to change the 1st tab, which will then change the other tabs. When I import the 2nd tab, the resultant sas dataset column names are all "A".  Is there a way for me to be able to import the tabs and still get the coluimn names? Thanks! ... View more

Hello, I am currently using SAS EG6.2 to manipulate/analyze the data of multiple input files (from excel).  The resulting dataset is about 1.2 millon rows.  I need to export that Excel 2010 for others to use for their purposes, such as creating pivot tables.  I use the EG function to export or export in the step, but it takes over a hour.  And sometimes it results in an error, which I think may be due to the fact that it's over 1 millon rows. 1. What is the most efficient way to export such a large dataset? 2.  I currently export as an excel (.xlsx) file.  Is it better to export as .txt, .csv or some other extension?  Are there any differences in the ability to use the file for pivot tables when it's .txt, .csv or .xlsx? Thank you! ... View more

Hello, Below is my PROC Tabulate code.  1.  For the columns, I would like group the B's together, C's together, etc. and create column headers. Current Result:  YTD14B FYF14B FYF15B FYF16B FYF17B FYF18B FYF5YB Would like: BENEFIT ($M) YTD14 FYF14 FYF15 FYF16 FYF17 FYF18 FYF5Y 2.  There are (3) Initiatives in the Class Initiatives.  They are called Initiative A, Initiative B and Initiative C.  Each program has X number of projects. Current code puts the total for each program at the bottom.  How do I put the total at the top of the program? How can I show on the report the following order:  Program C, Program A , Program B? To create distinction, I would like to add a border around (1) Initiative C & Benefit, (2) Initiative C & Cost, (3) Initiative B & Benefit, etc.  3.  Can I put (3) datasets into one report page? 4.  I have QUALIFIER in the row dimension.  The Qualifier Total = Initiative C Total + Initiative A Total + Initiative B Total.  How can I get rid of the left column that shows Qualifier but also keep the Qualifier Total row? Version BENEFIT ($M) COST ($M) YTD14 FYF14 FYF15 FYF16 FYF17 FYF18 FYF5Y YTD14 FYF14 FYF15 FYF16 FYF17 FYF18 FYF5Y Qualifier Qualifier Total Initiative C Total Program C-1 Program C-2 Program C-3 Initiative B Total Program B-1 Program B-2 Program B-3 Initiative A Total Program A-1 Program A-2 Program A-3 Program ****************** PROC TABULATE DATA=abc      FORMAT=MILLION.      ;           VAR YTD14C FYF14C FYF15C FYF16C FYF17C FYF18C FYF5YC YTD14B FYF14B FYF15B FYF16B FYF17B FYF18B FYF5YB YTD14N FYF14N FYF15N FYF16N FYF17N FYF18N FYF5YN YTD14D FYF14D FYF15D FYF16D FYF17D FYF18D FYF5YD;      CLASS Initiative /   ORDER=UNFORMATTED MISSING;      CLASS Qualifier /ASCENDING ORDER=UNFORMATTED MISSING;      CLASS Program / ASCENDING ORDER=UNFORMATTED MISSING;      CLASS ProjectName /ASCENDING    ORDER=UNFORMATTED MISSING;      CLASS Version / ORDER=UNFORMATTED MISSING;      TABLE      /* Row Dimension */   Qualifier={LABEL="" STYLE(CLASSLEV)={BACKGROUND=#D6D6D6 cellwidth=50}}*(     ALL={LABEL="Plan"}*{STYLE={FONT_WEIGHT=BOLD FONT_STYLE=ROMAN BACKGROUND=#FFFFC4}}*       Sum={LABEL=""}     Initiative={LABEL="" STYLE={BACKGROUND=#99CCFF}}*(       Program={LABEL="" STYLE={BACKGROUND=#99CCFF}}*         Sum={LABEL=""}       ALL={LABEL="Total"}*{STYLE={FONT_WEIGHT=BOLD FONT_STYLE=ROMAN BACKGROUND=#D6D6D6}}*         Sum={LABEL=""})),      /* Column Dimension */      YTD14C      FYF14C      FYF15C      FYF16C      FYF17C      FYF18C      FYF5YC      YTD14B      FYF14B      FYF15B      FYF16B      FYF17B      FYF18B      FYF5YB      YTD14N      FYF14N      FYF15N      FYF16N      FYF17N      FYF18N      FYF5YN      YTD14D      FYF14D      FYF15D      FYF16D      FYF17D      FYF18D      FYF5YD      /*Table Options */      / MISSTEXT=""        ;            ; RUN; ... View more

In my data, there is a variable called "TYPE", with 3 sub-types.  I created a report using List Report Wizard in SAS EG.  However, the resultant report view has each subtype on a page.  See rows 1-41 in the attached file.  I would like it to look like the table in row 49.  I am added callouts/comments in the file regarding what calculations I need to do. Type = needs to be grouped by columns Version, Forecast Qualiifer, InitiativeType and Program = needs to be grouped by rows Below is the code generated by SAS, with some modifications on my part. Any help is much appreciated! ***************************************************** data overall_report; set AAA; Version_2=Version; InitiativeType_2=InitiativeType; ForecastQualifier_2=ForecastQualifier; run; proc report data=jen1.overall_report nowd;   column Type Version ForecastQualifier InitiativeType Version_2 ForecastQualifier_2 InitiativeType_2 Program FYF14, SUM=FYF14_SUM FYF15, SUM=FYF15_SUM FYF16, SUM=FYF16_SUM FYF5Y, SUM=FYF5Y_SUM;   define Type / group format=$CHAR7. missing noprint order=formatted;   break after Type / page;     compute before _page_;   text1= ''||trim(left(put(Type,$CHAR7.)));   if Type=' ' then text1=' ';   line text1 $;   endcomp;   define Version / group format=$CHAR8. 'Version' missing noprint order=formatted;   compute Version;   if Version ne ' ' then hold1=Version;   if Version eq ' ' then Version=hold1;   endcomp;   define ForecastQualifier / group format=$CHAR14. 'ForecastQualifier' missing noprint order=formatted;   compute ForecastQualifier;   if ForecastQualifier ne ' ' then hold2=ForecastQualifier;   if ForecastQualifier eq ' ' then ForecastQualifier=hold2;   endcomp;   define InitiativeType / group format=$CHAR15. 'InitiativeType' missing noprint order=formatted;   compute InitiativeType;   if InitiativeType ne ' ' then hold3=InitiativeType;   if InitiativeType eq ' ' then InitiativeType=hold3;   endcomp;   define Version_2 / group 'Version' format=$CHAR8. missing order=formatted;   /***Calc the sub-totals***/   compute Version_2;   if upcase(_break_) eq "VERSION_2" then do;   Version_2=' ';   end;   if upcase(_break_) eq "VERSION_2" then do;   Version=' ';   end;   if upcase(_break_)="VERSION_2" then do;   call define("Version_2", 'style', 'style=[pretext="Subtotal  "]');   end;   if _break_='_RBREAK_' then do;   call define("Version_2", 'style', 'style=[pretext="Total "]');   end;   endcomp;   define ForecastQualifier_2 / group 'ForecastQualifier' format=$CHAR14. missing order=formatted;   define InitiativeType_2 / group 'InitiativeType' format=$CHAR15. missing order=formatted;   define Program / group 'Program' format=$CHAR42. missing order=formatted;   define FYF14 / analysis SUM 'FYF14' format=MILLION. missing;   define FYF14_SUM / format=MILLION.;   define FYF15 / analysis SUM 'FYF15' format=MILLION. missing;   define FYF15_SUM / format=MILLION.;   define FYF16 / analysis SUM 'FYF16' format=MILLION. missing;   define FYF16_SUM / format=MILLION.;   define FYF5Y / analysis SUM 'FYF5Y' format=MILLION. missing;   define FYF5Y_SUM / format=MILLION.;   /***Add Grand Total Row***/   break before Version / summarize;   /***Add Sub-Total Rows***/   break before Version_2 / summarize;   run; quit; TITLE; FOOTNOTE; ... View more

Hello, I spent the past couple of days trying to build a report using Summary Tables (under Describe).  I've made some headways in my progress, but spent more hours than I think it should take me on the following items.  I would like to apologize if I exceeded the number of bullets allowed.  I am looking for assistance on how to complete this via EG.  The SAS-generated code is below in red.  I am currently using SAS EG 6.1. How do I show a blank cell where data is missing?  Today, I get a "." in the cell. How can I get the results all in one-page (fit to page) to pdf?  I added the options papersize, but that didn't work I have a Variable called FYF5Y.  However it does not show on the generated report.  This is missing in the Column Dimension below and I cannot figure out how to include it. I would like to get a Subtotal (by Initiative Type) and Total (by Forecast Qualifier).  I dragged the TOTAL (ALL) from the Class box and here is my result.  I changed the amount to $1.  I wasn't able to copy over the top rows.  Each of the gray column is a fiscal year.  Modifying this bullet.  After I submitted this posting, I figured out how to do this - thanks to a posting I found with Cynthia's response.  :smileygrin:  I would like to add a column at the end that is a subtraction of the first gray column from the last gray column.  I read on this community that this is not possible.  The thread was from 2012 so I am not sure if this is possible in the 6.1 EG version.  Also, under the Summary Tables task, I do not see a SUM in the available statistics to choose from - should I want to add columns. Is there a way to filter what I would like to show in the table?  Right now I am creating new dataset to include only what I want and build summary tables from that.  I don't believe this is the correct and efficient way to do this.  For example, can I filter out to show only Forecast (under Version)? Thank you! Version ForecastQualifier . . . . . . Forecast 2015 Total (ALL) Program A . . . . . . B C . . . . . . D . . . . . . E . . . . . . F . . . . . . G . . . . . . H . . . . . . I . . . . . . 2014 Total (ALL) A $1 $1 $1 $1 $1 . B $1 $1 $1 $1 $1 . C $1 $1 $1 $1 $1 . D $1 $1 $1 $1 $1 . E $1 $1 $1 $1 $1 . F $1 $1 $1 $1 $1 . G $1 $1 $1 $1 $1 . H $1 $1 $1 $1 $1 . I $1 $1 $1 $1 $1 . Here is the SAS-generated code: /* Start of custom user code. */ options orientation=landscape; options papersize=(11in 8.5in); /* End of custom user code. */ %_eg_conditional_dropds(WORK.STABSummaryTablesCOST_BENEFIT); /* -------------------------------------------------------------------    Run the tabulate procedure    ------------------------------------------------------------------- */ PROC TABULATE DATA=JEN1.COST_BENEFIT(FIRSTOBS=1 )   OUT=WORK.STABSummaryTablesCOST_BENEFIT(LABEL="Summary Tables  for JEN1.COST_BENEFIT")   FORMAT=MILLION.   ;   VAR FYF14 FYF15 FYF16 FYF17 FYF18 FYF19 FYF5Y;   CLASS Version / ORDER=UNFORMATTED;   CLASS ForecastQualifier / ORDER=UNFORMATTED DESCENDING;   CLASS InitiativeType / ORDER=UNFORMATTED;   CLASS Type / ORDER=UNFORMATTED;   CLASS Program / ORDER=UNFORMATTED DESCENDING;   TABLE /* Row Dimension */ Version*   ForecastQualifier*     Program*       Sum={LABEL=""}, /* Column Dimension */ FYF14 FYF15 FYF16 FYF17 FYF18 FYF19 /*Table Options */ / MISSTEXT="" ;   ; RUN; ... View more