ODS + Excel

[MaheshPeesari]

Hi All,

DDE works only on SAS windows,It will be not possible to run in batch mode.using DDE we can control the cell positions, if
i want to do the same thing without DDE ,how can i achieve it.Could any one of you plese explain a small example where
you have to write data on to same sheet, into different cell positions as macro values keep on changing.Please guuide me as how
i can do this.Are there any options in ODS.

Please find below example.

options noxwait noxsync;
/* Open Excel */
x "'C:\Program Files\Microsoft Office\Office12\excel.exe'";
filename cmds dde 'excel|system';

data test;
x=sleep(5);
run;

/* Open spreadsheet template in Excel */
data _null_;
file cmds;
put '[open("D:\NewDB\TEMPLATE Report 1.xlsm")]';
run;

%macro TRY(area=);
filename Excel DDE "Excel|&sheet.!r1c14:r2c14" notab;
DATA _NULL_;FILE Excel;
put &quarter;
put .
run;

filename Excel DDE "Excel|&sheet.!r14c5:r40c5" notab;

DATA _NULL_;
SET abc;
where area=&area;
FILE Excel;

  put abc  / ' ';
    put def;
  put  ; 
put ghi;
put  / ' ' / ' ' / ' ' / ' ' ; 
put jkl;
put mno;
put pqr;
put '  ';
put tuv;
  put   / ' ' / ' ' ;
put wxy / ' ';
put zab / ' ';
  put bac / ' ';
run;

filename Excel DDE "Excel|&sheet.!r14c6:r40c6" notab;

DATA _NULL_;
SET abc;
where area=&area;
FILE Excel;

  put abc  / ' ';
    put def;
  put  ; 
put ghi;
put  / ' ' / ' ' / ' ' / ' ' ; 
put jkl;
put mno;
put pqr;
put '  ';
put tuv;
  put   / ' ' / ' ' ;
put wxy / ' ';
put zab / ' ';
  put bac / ' ';
run;

filename Excel DDE "Excel|&sheet.!r14c2:r40c2" notab;

DATA _NULL_;
SET abc;
where area=&area;
FILE Excel;

  put abc  / ' ';
    put def;
  put  ; 
put ghi;
put  / ' ' / ' ' / ' ' / ' ' ; 
put jkl;
put mno;
put pqr;
put '  ';
put tuv;
  put   / ' ' / ' ' ;
put wxy / ' ';
put zab / ' ';
  put bac / ' ';
run;

filename Excel DDE "Excel|&sheet.!r14c3:r40c3" notab;

DATA _NULL_;
SET abc;
where area=&area;
FILE Excel;

  put abc  / ' ';
    put def;
  put  ; 
put ghi;
put  / ' ' / ' ' / ' ' / ' ' ; 
put jkl;
put mno;
put pqr;
put '  ';
put tuv;
  put   / ' ' / ' ' ;
put wxy / ' ';
put zab / ' ';
  put bac / ' ';
run;

%mend;

%try(Area=001);
%try(Area=002);

Hiw can i achieve ot without using above code.Please guide me.

Thanks,

Mahesh P

[Cynthia_sas]

Hi:

  In the past, I have had some students tell me that they make a CSV file using ODS and then populate a pre-existing Excel template file using the CSV file.

  Generally, however, when ODS creates a file, if the file exists with the same name, ODS will write over the file and essentially re-create it. So, for example, if you have a workbook with a sheet for January data and you want to add February's data, in the Excel way of solving the problem, you would make a new sheet every month by manually editing the workbook (or using DDE, ODBC or OLE-DB or .NET). In the ODS way of solving the problem, you would recreate the workbook every month. So when you needed January, your program would only create a workbook with a sheet for January. Then, when you had data for February, your program would create a workbook with 2 months of data: a sheet for January and a sheet for February. Then, in March, your program would create the workbook again, this time with 3 sheets: January, February and March. If your data are stored in SAS, running the same report for different months is as easy as adding a where statement to your program (some examples):

where month = 1;

where month in (1, 2);

where month in (1,2,3);

where month le 4;

based on the above WHERE statements, then a simple BY MONTH in your code (if you used ODS TAGSETS.EXCELXP) would generate a separate sheet for every month in your data. If you are only generate a new COLUMN in the same sheet every month, this is probably an easier thing to do with SAS and ODS. Generally, SAS will only make columns on a report for the months that it has. So, following the same data...in January, you can have a report with only a column for January. Then in February, instead of adding just a column for February, you recreate the report with a separate column for each MONTH value. Then in March, if you have 3 months in your data, you will get 3 columns on the report. In April, you'll have 4 months in your data and you will get 4 columns on the report. But with ODS, you would be generating a new report and your program code would control the MONTH values that you would see on the report.

I can't visualize what you are trying to create from your program. It looks like you are populating an Excel macro-enabled workbook (.XLSM)? I thought that Excel template files were .XLT files. What does your report template file look like? It is nearly impossible to answer your question about ODS because what you will need to recreate with ODS and SAS is the report layout that is in the Excel template file or workbook that you are populating. Your program doesn't convey that piece of information. Can you post a screenshot of what the final Excel file looks like?

Other useful information would be some idea of what your data represents, possibly with a small sample of the data and/or a description of what the process and how the workbook is used. For example, in the above scenario, it is easy to recreate a sheet for January using ODS, if you know that January's data has not changed. If Excel is your only repository for January's data; however and if you allow folks to CHANGE January's data once it is in the worksheet, then you either have to read January's data back into SAS, so the changes can be included when February's data is added to the report, in the form of a worksheet.

This raises other issues/questions/complications: Is the data in a SAS dataset or some other format readable by SAS (such as Oracle, DB2, SQL server, etc)? Or, if you allow users to change the data in Excel, do these changes ever get back to the original data source.

The fact that you have different values for SAS Macro variables like &QUARTER and &PERIOD wouldn't be much of an issue, you can use macro variable values in SAS reporting procedures, like PROC REPORT, PROC TABULATE, etc.

Without more information from you, it is hard to make a more constructive suggestion. There are lots of user group papers about using ODS TAGSETS.EXCELXP -- a Google search should find a lot of hits. That might be one place to start.

cynthia

[SASKiwi]

Do you have SAS/ACCESS to PC Files licensed? If so then using the Excel LIBNAME option and the DATA step is another option to explore and it may get you close to what you want. The coding required would be a lot simpler.