topic Re: Creating excel-files with SAS-EG in Microsoft Integration with SAS

Creating excel-files with SAS-EG

Liej — Fri, 14 Dec 2012 15:27:38 GMT

Hey guys,

I was wondering if there's a solution for my problem.

I need to create a lot of excel-files, more then 300, based on SAS-data.

There's an extra difficulty, the data in the excel-files need to be displayed

in a specific way.

This is the code I use to create my excel-files. (attachment)

The only problem I have is that the excel-files are created on the SAS-server.

Can I change my code so the excel-files are created on my PC or a shared folder?

David

Re: Creating excel-files with SAS-EG

Doc_Duke — Fri, 14 Dec 2012 16:42:37 GMT

Enterprise Guide is a client-server architecture. When you run SAS PROCs, they are run on the server and can only get to directories known to that server. There is a work-around to copy the files back to your PC. See

There and back again: copying files in SAS Enterprise Guide - The SAS Dummy

Doc Muhlbaier

Duke

Re: Creating excel-files with SAS-EG

Liej — Tue, 18 Dec 2012 08:33:18 GMT

I made a little pdf-print from an excel-file example.

The green fields I need to take from a SAS-dataset and the blue fields are

calculated.

Is there an easier way to make such an excel-file with a lay-out like that

based on a SAS-dataset?

Re: Creating excel-files with SAS-EG

Cynthia_sas — Tue, 18 Dec 2012 23:14:59 GMT

Hi:

Does PROC REPORT create the worksheet that you show? If you are already using PROC REPORT, then using a user-defined format and/or CALL DEFINE techniques, you could apply the colors and font changes to your output.

Without getting into CALL EXECUTE, here's a simple PROC REPORT that does highlighting on the report rows based on variable values.

cynthia

proc format;
value agef 11-12='yellow'
             13-14='cyan'
             15-16='cxdddddd';
run;

ods tagsets.excelxp file='c:\temp\hilite.xls' style=meadow;

proc report data=sashelp.class nowd;
title 'Highlighting Conditionally';
column name age height weight sex;
define name / order;
define age / display
        style(column)={background=agef.};
define height / display;
define weight / display;
define sex / display;
compute sex;
   if substr(name,1,1) = 'J' and
      sex = 'F' then do;
      call define('name','style','style={background=pink}');
      call define(_col_,'style','style={background=pink}');
   end;
   else if substr(name,1,1) = 'J' and
      sex = 'M' then do;
      call define('name','style','style={background=lightblue}');
      call define(_col_,'style','style={background=lightblue}');
   end;
endcomp;
compute height;
    ** color height based on age;
    colorval = put(age,agef.);
    call define (_col_,'style','style={background='||trim(colorval)||'}');
endcomp;
run;
ods tagsets.excelxp close;

Re: Creating excel-files with SAS-EG

Liej — Wed, 19 Dec 2012 09:07:16 GMT

The worksheet showen in the pdf is created in Excel, and that's how the SAS-output should be. (fische excel.xls)

I don't need to have colors in my output, it was only to indicate the different type of fields.

Green fields are data taken from SAS, blue field are calculated and the white fields are tekst.

The excel-file is based on a SAS-dataset like this table. The data is sorted based on Number (asc) and

Start_date (desc).

Number	Start_date	Date_Fin	Code_1	Code2
12345	01-06-2012	30-06-2012	76	4
12345	01-05-2012	31-05-2012	85	5
12345	01-04-2012	30-04-2012	80	4
12345	01-03-2012	31-03-2012	83	5
12345	01-02-2012	29-02-2012	79	3
12345	01-01-2012	31-01-2012	81	4
45678	01-05-2012	30-06-2012	80	6
45678	01-03-2012	30-04-2012	79	5
45678	01-01-2012	29-02-2012	83	7
78912	01-06-2012	30-06-2012	80	5

Based on this table I should create 3 excel-files IFI_12345, IFI_45678 and IFI_78912.

Can I do this with Proc Report or should I keep using the call execute?

temp.xml is an example created with the call execute methode. As you can see there is no layout.