Date:         Mon, 16 Jul 2007 08:17:08 -0700
Reply-To:     Franz <franz_cl2003@YAHOO.FR>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Franz <franz_cl2003@YAHOO.FR>
Subject:      Re: PROC SQL: Except (URGENT please)
In-Reply-To:  <200707161422.l6GAmGtU006714@mailgw.cc.uga.edu>
Content-Type: text/plain; charset=iso-8859-1

Thank you all for your mails.
The point is I am still have to solve the problem.

Arild when One and Two in the SQL statement are from
the same raw data file then everything is ok. In my
case A is an already existing dataset and B is from my
raw file. Even when I proc print both A and B I have
similar content on the screen. But my SQL (with
except) is still not working. I guess a carriage
return character at the end of the variable values is
causing the problem. How do I prevent SAS from reading
such a character. Or how do I delete the carriage
return character from my values.

Thank you,
Franz.




--- Arild S <sko@KLP.NO> wrote:

> On Mon, 16 Jul 2007 06:08:06 -0700, Franz
> <franz_cl2003@YAHOO.FR> wrote:
> >Dear all,
> >
> >Here is a sample of the data file I am reading d
> from.
> >The first 3 fields are not that important for my
> task.
> >
> >
> >01020   XXYY12AAA       XX12CCCCC       (R) EMPYEMA
> >01021   XXYY12BBB       XX12CCCCC       ARTHRITIS
> RIGHT PULS
> >
> >
> >
> >In my file the variable d I am having trouble with
> >hlod the values:
> >R) EMPYEMA
> >ARTHRITIS RIGHT PULS)
> >
> >After reading the raw data file into a dataset B I
> >then execute my SQL statement (see bellow) which is
> >not working properly, even though those 2 values of
> d
> >(above) are also present in A
> >
> >
> >proc sql;
> >create table Final as
> >     select * FROM A
> >except
> >     select * FROM B
> >;
> >quit;
> >
> >
> >Please help. Thanks.
> >Franz.
> >
>
> I don't know.. I ran this:
>
> 205  data one two;
> 206      infile 'C:\temp\tabs.txt' dlm = '09'x;
> 207      length A B C D $100;
> 208      input A B C D;
> 209  run;
>
> NOTE: The infile 'C:\temp\tabs.txt' is:
>       File Name=C:\temp\tabs.txt,
>       RECFM=V,LRECL=256
>
> NOTE: 2 records were read from the infile
> 'C:\temp\tabs.txt'.
>       The minimum record length was 37.
>       The maximum record length was 46.
> NOTE: The data set WORK.ONE has 2 observations and 4
> variables.
> NOTE: The data set WORK.TWO has 2 observations and 4
> variables.
> NOTE: DATA statement used (Total process time):
>       real time           0.01 seconds
>       cpu time            0.01 seconds
>
>
> 210
> 211  proc sql;
> 212      delete from two where D = '(R) EMPYEMA';
> NOTE: 1 row was deleted from WORK.TWO.
>
> 213      select D from one
> 214      except
> 215      select D from two;
> 216  quit;
>
> which correctly produces this output:
>
> D
> ------------
> (R) EMPYEMA
>
> It's not an upper/lower case thing?!
> You've got only one variable in A and B ?
> You do know "select *" compares the entire row/ all
> vars?
> Other than that.. blanks maybe?
>
> A
>




____________________________________________________________________________________Ready for the edge of your seat?
Check out tonight's top picks on Yahoo! TV.
http://tv.yahoo.com/