Date: Mon, 16 Jul 2007 12:00:45 -0400
Reply-To: "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Subject: Re: PROC SQL: Except (URGENT please)
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.
>--- 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?
for the edge of your seat?
>Check out tonight's top picks on Yahoo! TV.