Date: Thu, 19 Jul 2007 00:15:05 -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) Thanks!
In-Reply-To: <200707161600.l6GAnNjE027841@malibu.cc.uga.edu>
Content-Type: text/plain; charset=iso-8859-1
Thank you all so much!
Everything is working as expected.
Regards,
Franz.
--- "Howard Schreier <hs AT dc-sug DOT org>"
<nospam@HOWLES.COM> wrote:
> On Mon, 16 Jul 2007 08:17:08 -0700, Franz
> <franz_cl2003@YAHOO.FR> wrote:
>
> >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.
>
> Specify a HEX or $HEX format in the PROC PRINT. Then
> see if the values which
> should be identical (not "similar") are in fact
> identical.
>
> >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/
>
____________________________________________________________________________________
Moody friends. Drama queens. Your life? Nope! - their life, your story. Play Sims Stories at Yahoo! Games.
http://sims.yahoo.com/
| 
