Date:         Sat, 14 Jan 2006 11:10:14 -0500
Reply-To:     Edzard van Santen <evsanten@ACESAG.AUBURN.EDU>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Edzard van Santen <evsanten@ACESAG.AUBURN.EDU>
Subject:      Re: Why not use macros ?
Comments: To: Paul Choate <pchoate@DDS.CA.GOV>

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