LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (July 2003, week 1)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Fri, 4 Jul 2003 11:27:04 +0100
Reply-To:     Peter Crawford <Peter@CRAWFORDSOFTWARE.DEMON.CO.UK>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Peter Crawford <Peter@CRAWFORDSOFTWARE.DEMON.CO.UK>
Organization: Crawford Software Consultancy Limited
Subject:      Re: Excel output file with label and numeric format problems

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


Back to: Top of message | Previous page | Main SAS-L page