SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

Creating excel-files with SAS-EG

Reply
Occasional Contributor
Posts: 14

Creating excel-files with SAS-EG

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

Attachment
Valued Guide
Posts: 2,106

Re: Creating excel-files with SAS-EG

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

Occasional Contributor
Posts: 14

Re: Creating excel-files with SAS-EG

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?

SAS Super FREQ
Posts: 8,645

Re: Creating excel-files with SAS-EG

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;
    

Occasional Contributor
Posts: 14

Re: Creating excel-files with SAS-EG

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).

NumberStart_dateDate_FinCode_1Code2
1234501-06-201230-06-2012764
1234501-05-201231-05-2012855
1234501-04-201230-04-2012804
1234501-03-201231-03-2012835
1234501-02-201229-02-2012793
1234501-01-201231-01-2012814
4567801-05-201230-06-2012806
4567801-03-201230-04-2012795
4567801-01-201229-02-2012837
7891201-06-201230-06-2012805

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.

Post a Question
Discussion Stats
  • 4 replies
  • 790 views
  • 6 likes
  • 3 in conversation