|
Endorsing what big-WWV (William W. Viergever <wwvierg@ATTGLOBAL.NET>)
is suggesting below , may I add two points:
1
If you don't or can't have excel on-line, (batch or UNIX, etc) you can
as easily write a csv file, to be delivered/loaded to excel later.
2
You can generalise nearly all the code, until only the input data set
name and the output file name need be given.
%let ds = sashelp.gnp; * create csv for this example data;
%let output_file = 'your preferred csv file name.csv' ;
Handling formats is simple -
Apply these to your dataset directly, or in a view, if the data
is read-only. They are respected by this routine. If you want
to deliver the highest precision for real numbers, use format 32.,
but ensure date and time formatting will be understood by excel,
which does not use the 01JAN1960 start point.
The more complex part is generalising the creation of the heading
line. I assume you want label if present, otherwise the variable
name. Here is some code for that
%let header = " "; * default empty, the code should load this;
%let dlm = ', '; * whatever delimiter is required;
proc sql noprint;
option _last_ = &ds; * here I resolve one level names;
%let lib=%scan( &syslast,1,.); * to ensure libname can be extracted;
%let mem=%scan( &syslast,2,.); * but memname must ;
%let mem= %upcase(&mem); * be upcase-ed ;
create table metadata_info as
select case when label ne ' ' then label
else name
end as heading
, *
from dictionary.columns
where libname= "&lib" & memname= "&mem"
order by varnum ;
select quote( quote( trim( heading )))
into :header separated by " &dlm "
from metadata_info
order by varnum ;
%let n_cols = &sqlobs;
quit;
*Then the creation of the csv file is only 9 statements;
data _null_;
file &output_file lrecl=32000 dsd dlm=&dlm;
put &header ;
do until( eof );
set &ds end=eof ;
put (_all_)(~); /* the dsd file option generates delimiters
~ protects against "quotes or delimiters in data*/
end;
stop;
run;
William W. Viergever <wwvierg@ATTGLOBAL.NET> writes
>At 10:58 AM 07/03/2003 -0700, Helen wrote:
>
>>Dear SAS-L,
>>
>>I would like to export sas to Excel with variable label and correct
>>numeric values. By using Proc export, I can get correct numeric
>>variable values, but no label. By using Proc dbload, I can get
>>variable label in Excel but the numeric values are changed into
>>xxxx.00 format which I don't like. Is there another way to make the
>>output correctly? Thanks in advance.
>>
>>The following is an example:
>>
>>data class;
>> set sashelp.class;
>> label name='Student Name'
>> sex='Gender'
>> age='Age of 2003'
>> weight='Weight of 2003'
>> Height='Height of 2003';
>>run;
>>PROC DBLOAD DBMS=EXCEL DATA=class;
>> PATH='c:\temp\class.xls';
>> PUTNAMES=YES;
>> label;
>> RESET ALL;
>> LOAD ;
>>RUN ;
>>
>>Student Name Gender Age of 2003 Height of 2003 Weight of 2003
>>Alfred M 14.00 69.00 112.50
>>Alice F 13.00 56.50 84.00
>>Barbara F 13.00 65.30 98.00
>>Carol F 14.00 62.80 102.50
>>Henry M 14.00 63.50 102.50
>>James M 12.00 57.30 83.00
>>Jane F 12.00 59.80 84.50
>>Janet F 15.00 62.50 112.50
>>Jeffrey M 13.00 62.50 84.00
>>John M 12.00 59.00 99.50
>>Joyce F 11.00 51.30 50.50
>>Judy F 14.00 64.30 90.00
>>Louise F 12.00 56.30 77.00
>>Mary F 15.00 66.50 112.00
>>Philip M 16.00 72.00 150.00
>>Robert M 12.00 64.80 128.00
>>Ronald M 15.00 67.00 133.00
>>Thomas M 11.00 57.50 85.00
>>William M 15.00 66.50 112.00
>
>
>When in doubt, DDE!
>
>1) First create/open "name_of_your_Excel_file.xls"
>
>2) Run this code (Note: check requisite field widths/lengths for your
>character variables):
>
>filename tab1 dde "excel|[name_of_your_Excel_file.xls]Sheet 1!r1c1:r20c5"
>notab lrecl=1024 ;
>data _null_ ;
> set class ;
> file tab1 ;
> if _n_ = 1 then put @1
> 'Student Name' '09'x
> 'Gender' '09'x
> 'Age of 2003' '09'x
> 'Height of 2003' '09'x
> 'Weight of 2003' ;
> put @1
> name $7. '09'x
> sex $1. '09'x
> age 3. '09'x
> height 2. '09'x
> weight 3. ;
>run;
>
>As far as the numeric formats are concerned, I would simply format them to
>desired formats once in Excel.
>
>HTH,
>
>Out
>
>
>
>
>------------------------------------------------------------
>William W. Viergever Voice : (916) 483-8398
>Viergever & Associates Fax : (916) 486-1488
>2920 Arden Way Suite N E-mail : wwvierg@attglobal.net
>Sacramento, CA 95825
>------------------------------------------------------------
--
Peter Crawford
available for SAS consultancy contracts
| 
