Date:         Sat, 12 Nov 2005 13:49:58 -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: Importing to SAS from MS Excel
Comments: To: sas-l@uga.edu

Sahuquillo,

Chuck may have misundertood what you are trying to do but, in any case, I'd
like to know if there is a function that accomplishes what Chuck had
suggested.  Regardless, here is what I think you are looking for.

If you are trying to remove "rows" which contain all missing values, and all
of the variables are numeric, then you could use something like:

proc sql noprint;
  select name into :Varlist separated by ','
   from sashelp.vcolumn
    where libname='SASUSER' and memname='IAP';
quit;

data SASUSER.IAP (drop=allmissing);
  set SASUSER.IAP;
  allmissing=nmiss(&Varlist.);
  if allmissing le 3;
run;

If some or all of your variables are character, then you would have to
modify the above, using the 'missing' rather than the 'nmiss' function.

Of course, the statement 'allmissing le 3' would have to be changed to
reflect your actual number of variables-1.

Conversely, if Chuck was right and you wanted to remove columns which only
contained missing values, as long as none of your variables start with a
capital letter F (since the import procedure will assign unnamed columns as
F1, F2, etc.), you could use something like the following:

proc sql noprint;
  select name into :Varlist separated by ' '
   from sashelp.vcolumn
    where libname='SASUSER' and memname='IAP'
     and substr(name,1,1) NE 'F';
quit;

data SASUSER.IAP;
  set SASUSER.IAP (keep=&varlist.);
run;

HTH,
Art
-------------
"Sahuquillo" <bangali.doctor@gmail.com> wrote in message
news:1131529844.270645.305160@g47g2000cwa.googlegroups.com...
> Hi,
>
> I have an excel table with 180 rows filled in, When suing Proc IMPORT
> as folows:
> PROC IMPORT OUT= SASUSER.IAP
>             DATAFILE= "E:\MY EXCEL DOCS\IAP2005.xls"
>             DBMS=EXCEL REPLACE;
>      SHEET="'2003$'";
>      GETNAMES=YES;
>      MIXED=YES;
>      SCANTEXT=YES;
>      USEDATE=YES;
>      SCANTIME=YES;
> RUN;
>
> I get all the empty rows in the SAS data set. How can only import rows
> that have variables filled in and avoid to import empty rows ?
>
> Thank you
>