Date:         Wed, 16 May 2007 06:38:24 -0400
Reply-To:     Jim Groeneveld <jim2stat@YAHOO.CO.UK>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Jim Groeneveld <jim2stat@YAHOO.CO.UK>
Subject:      Re: reading excel spreadsheets using proc import
Comments: To: dcmapp@GMAIL.COM

Hi D_SAS,

You say your SAS code won't run, but you do not explicate what it should do,
what it does, what it doesn't do, what SAS reports to the log or rather the
relevant part of the log itself. Actually your code runs, but it does not
run as intended. I'll show you why (two causes) and how to improve the code.

1. Macro variables within single quotes don't resolve, so your statement
SHEET="'Raw Data &num.'";
should be redesigned as (whereas it has too many quotes):
SHEET="Raw Data &num.";
This is an essential cause for not working.

2. You could do without the data step creating an empty dataset:
DATA view_DATA_Air; RUN;
and replace your later data step
DATA VIEW_DATA_AIR; set VIEW_DATA_VIEW View_Data; RUN;
by PROC APPEND.

3. In that last data step (pt. 2) you do not add the new Excel data (dataset
View_Data) to the already existing dataset VIEW_DATA_AIR, but to the not yet
existing dataset VIEW_DATA_VIEW. I think this is another essential mistake.

4. You could write to a permanent dataset (VIEW.View_DATA) directly and
append that one, while removing the statement
IF P_C = "" THEN DELETE;
Instead your statement
PROC IMPORT OUT=View_Data;
could read
PROC IMPORT OUT=View_Data (WHERE=(P_C NE ""));

5. Instead of 7 macro calls you might have just one with a loop within the
macro: %DO Num = 1 %TO 7; ............; %END;

Just a remark. From your name giving it seems that you are working with SAS
views, but at further sight it appears you are not. May be confusing.

Regards - Jim.
--
Jim Groeneveld, Netherlands
Statistician, SAS consultant
home.hccnet.nl/jim.groeneveld


On Wed, 16 May 2007 02:43:58 -0700, d_sas <dcmapp@GMAIL.COM> wrote:

>Hi All -
>
>I'm having considerable trouble using the following SAS code (won't
>run at all at this point).  As a new user, I can't quite figure out
>what is going on with this code, and I am attempting to use macros.
>I'm trying to read in 7 worksheets from a single excel workbook.  Once
>this is done, my goal is to join the individual datasets into one
>dataset.  Please provide guidance at your convenience!
>
>Thank you.
>
>
>libname view 'C:\PD\View Datasets';
>
>DATA view_DATA_Air;
>RUN;
>
>%MACRO viewed(&num);
>
>PROC IMPORT OUT=View_Data;
>            DATAFILE= "C:\PD\view_Air.xls"
>            DBMS=EXCEL REPLACE;
>     SHEET="'Raw Data &num.'";
>     GETNAMES=YES;
>     MIXED=NO;
>     SCANTEXT=YES;
>     USEDATE=YES;
>     SCANTIME=YES;
>RUN;
>
>DATA VIEW_DATA_AIR;
>   set VIEW_DATA_VIEW View_Data;
>RUN;
>
>%MEND viewed;
>
>%viewed(1);
>%viewed(2);
>%viewed(3);
>%viewed(4);
>%viewed(5);
>%viewed(6);
>%viewed(7);
>
>DATA VIEW.View_DATA;
>SET VIEW_DATA_AIR;
>IF P_C = "" THEN DELETE;
>RUN;