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)
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.
--- 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
> >The first 3 fields are not that important for my
> >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
> >(above) are also present in A
> >proc sql;
> >create table Final as
> > select * FROM A
> > select * FROM B
> >Please help. Thanks.
> 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,
> NOTE: 2 records were read from the infile
> The minimum record length was 37.
> The maximum record length was 46.
> NOTE: The data set WORK.ONE has 2 observations and 4
> NOTE: The data set WORK.TWO has 2 observations and 4
> NOTE: DATA statement used (Total process time):
> real time 0.01 seconds
> cpu time 0.01 seconds
> 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:
> (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
> Other than that.. blanks maybe?
____________________________________________________________________________________Ready for the edge of your seat?
Check out tonight's top picks on Yahoo! TV.