LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (July 2007, week 3)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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/


Back to: Top of message | Previous page | Main SAS-L page