Date:         Sat, 14 Jan 2006 13:07:02 -0500
Reply-To:     Arthur Tabachneck <art297@NETSCAPE.NET>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Arthur Tabachneck <art297@NETSCAPE.NET>
Subject:      Re: Why not use macros ?
Comments: To: Edzard van Santen <evsanten@ACESAG.AUBURN.EDU>

Edzard,

Sounds like another Excel import problem that was posted earlier in the
week, namely that SAS isn't reviewing enough records to determine that a
column has mixed data.  In your case, it might even be that one worksheet
has just numeric data for a particular column, while another has either
alphanumeric or mixed.

Two solutions were proposed, if I recall correctly, one changing the
guessingrows setting in the registry, and another adding a dummy first row
to each spreadsheet (with alpha characters) to force SAS to expect mixed
data in the problematic columns.

HTH,
Art
----------
On Sat, 14 Jan 2006 11:10:14 -0500, Edzard van Santen
<evsanten@ACESAG.AUBURN.EDU> wrote:

>Paul,
>Thanks for the suggestion. It's slick but I am getting the following error
>message: "Variable XYZ has been defined as both character and numeric".
The
>cause of the problem are missing obs in the source file that were coded
with
>the standard period. I thought I could solve the problem by including
>MIXED=YES into the libname statement but that didn't work. Any
suggestions.
>
>EvS
>
>
>
>On Fri, 13 Jan 2006 15:16:09 -0800, Choate, Paul@DDS <pchoate@DDS.CA.GOV>
wrote:
>
>>Edzard -
>>
>>I'd use a libname excel engine and a single datastep to read the files.
>>
>>libname VT 'C:\Corn\';
>>libname exbk excel  'C:\Corn\Corndat05.xls' ;
>>
>>DATA vt.Corn;
>>     SET exbk.'Start$'n   exbk.'TVS_REG$'n exbk.'SMS_REG$'n
>>         exbk.'TVS_IRR$'n exbk.'PEF_REG$'n exbk.'EVS_NOT$'n
>>         exbk.'BEF_REG$'n exbk.'GCS_REG$'n exbk.'WGS_REG$'n
>>         exbk.'WGS_IRR$'n exbk.'SMS_PRE$'n exbk.'SMS_EAR$'n
>>         exbk.'EVS_EAR$'n exbk.'GCS_EAR$'n ;
>>run;
>>libname exbk clear;
>>
>>
>>I might create the list in the set statement with some sort of macro.
>>It depends on whether the list is variable or static.
>>
>>hth
>>
>>Paul Choate
>>DDS Data Extraction
>>(916) 654-2160
>>
>>-----Original Message-----
>>From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
>>Edzard van Santen
>>Sent: Friday, January 13, 2006 3:00 PM
>>To: SAS-L@LISTSERV.UGA.EDU
>>Subject: Re: Why not use macros ?
>>
>>OK folks,
>>Look at the following simple (simplistic) macro I use to take care of
>>repetitive business. I am ready and willing to learn how to do it
>>better.
>>
>>EvS
>>
>>options nocenter ps=5000 ls=240 ;
>>libname VT 'C:\Corn\';
>>proc import datafile='C:\Corn\Corndat05.xls'
>>   out=vt.Corn dbms=excel2000 replace;
>>   sheet = "Start";
>>run;
>>%macro import(sheet);
>>proc import datafile='C:\Corn\Corndat05.xls'
>>   out=import dbms=excel2000 replace;
>>   sheet = "&sheet";
>>proc datasets nolist;
>>   append base=vt.Corn data=import force;
>>run;
>>%mend import;
>>* this list contains the worksheet names to be imported and appended  ;
>>%import(TVS_REG);
>>%import(SMS_REG);
>>%import(TVS_IRR);
>>%import(PEF_REG);
>>%import(EVS_NOT);
>>%import(BEF_REG);
>>%import(GCS_REG);
>>%import(WGS_REG);
>>%import(WGS_IRR);
>>%import(SMS_PRE);
>>%import(SMS_EAR);
>>%import(EVS_EAR);
>>%import(GCS_EAR);