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
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
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?
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;
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.