SAS Enterprise guide Query

Hi,
I am trying to run a query in SAS enterprise guide.
I am trying two criteria on one field. I am trying to use advance filter with or statement but it is not giving me the correct output.

For example :
Field Name : Tax_Statement
Data : Post Or None

Expression:
Tax_Statement = 'Post' Or 'Non'
this is NOT GIVING me correct extract,
please help.
cheers
premkripalaniAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aloysius LowCommented:
if you are using advance query, you have to type the entire condition:
tax_statement = 'Post' or tax_statement = 'None'

if this is not resolving your problem, please copy and paste the generated query from the EG node. it will make it easier to help.
0
premkripalaniAuthor Commented:
Code 1.
PROC SQL;
   CREATE TABLE WORK.TAX_RAW AS
   SELECT t1.MEMBSHIP_ID,
          t3.TAXSTATEMENT_CONT_METH
      FROM MKMASTER._MEMBERS_INFO_ AS t1, MKMASTER._MEMBSHIP_INFO_ AS t2, MKMASTER._CONTACT_INFO_ AS t3
      WHERE (t3.TAXSTATEMENT_CONT_METH = 'POST');
QUIT;
When I run the above code 1 I get 50000 records


Code 2:
PROC SQL;
   CREATE TABLE WORK.TAX_RAW AS
   SELECT t1.MEMBSHIP_ID,
          t3.TAXSTATEMENT_CONT_METH
      FROM MKMASTER._MEMBERS_INFO_ AS t1, MKMASTER._MEMBSHIP_INFO_ AS t2, MKMASTER._CONTACT_INFO_ AS t3
     WHERE (t3.TAXSTATEMENT_CONT_METH = 'POST' OR t3.TAXSTATEMENT_CONT_METH = ‘ ‘);
QUIT;
WHEN I run above code I get all the records which is wrong.

When I run separate filter on “Post” and “ “ , then I get correct two outputs.
Please let me know if you need more information.

0
premkripalaniAuthor Commented:
I want to filter recordds on "Post" OR " " (Blank).
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Aloysius LowCommented:
can you please produce the log for both your codes?

i'm thinking it's because of a cartesian product join which your code invariably produces as it does not state the joining conditions of t1, t2 and t3.
0
premkripalaniAuthor Commented:
16         PROC SQL;
17            CREATE TABLE WORK.TAX_RAW AS
18            SELECT t1.MEMBSHIP_ID,
19                   t2.GROUP_TYPE,
20                   t1.RELATIONSHIP,
21                   t1.TITLE,
22                   t1.FIRST_NAME,
23                   t1.SURNAME,
24                   t1.GENDER,
25                   t1.LOB,
26                   t3.LIVINGWELL_CONT_METH,
27                   t3.TAXSTATEMENT_CONT_METH
28               FROM MKMASTER._MEMBERS_INFO_ AS t1, MKMASTER._MEMBSHIP_INFO_ AS t2, MKMASTER._CONTACT_INFO_ AS t3
29               WHERE (t1.MEMBSHIP_ID = t2.MEMBSHIP_ID AND t2.MEMBSHIP_ID = t3.MEMBSHIP_ID AND t1.MEMBSHIP_ID = t3.MEMBSHIP_ID) AND
30                     (t2.GROUP_TYPE = 'RETAIL' AND t1.RELATIONSHIP = 'H' AND t1.LOB NOT = 'ALI' AND t3.LIVINGWELL_CONT_METH =
31                    'EMAIL' AND t3.TAXSTATEMENT_CONT_METH = 'POST' OR '');
NOTE: Compressing data set WORK.TAX_RAW decreased size by 41.90 percent.
      Compressed is 484 pages; un-compressed would require 833 pages.
NOTE: Table WORK.TAX_RAW created, with 50777 rows and 10 columns.

32         QUIT;
NOTE: PROCEDURE SQL used (Total process time):
      real time           1:24.34
      cpu time            13.09 seconds
     

33        
34        
35         GOPTIONS NOACCESSIBLE;
36        
37        
38        
39        
40         %LET _CLIENTTASKLABEL=;
41         %LET _CLIENTPROJECTPATH=;
42         %LET _CLIENTPROJECTNAME=;
43        
2                                                          The SAS System                              14:21 Tuesday, April 20, 2010

44         ;*';*";*/;quit;run;
45         ODS _ALL_ CLOSE;
46        
47        
48         QUIT; RUN;
0
Aloysius LowCommented:
the problem with the code really lies in the last part of the query ... "OR ''".

if executed separately, i.e.
WHERE (t1.MEMBSHIP_ID = t2.MEMBSHIP_ID AND t2.MEMBSHIP_ID = t3.MEMBSHIP_ID AND t1.MEMBSHIP_ID = t3.MEMBSHIP_ID) AND (t2.GROUP_TYPE = 'RETAIL' AND t1.RELATIONSHIP = 'H' AND t1.LOB NOT = 'ALI' AND t3.LIVINGWELL_CONT_METH = 'EMAIL' AND t3.TAXSTATEMENT_CONT_METH = 'POST');
and
WHERE (t1.MEMBSHIP_ID = t2.MEMBSHIP_ID AND t2.MEMBSHIP_ID = t3.MEMBSHIP_ID AND t1.MEMBSHIP_ID = t3.MEMBSHIP_ID) AND (t2.GROUP_TYPE = 'RETAIL' AND t1.RELATIONSHIP = 'H' AND t1.LOB NOT = 'ALI' AND t3.LIVINGWELL_CONT_METH = 'EMAIL' AND t3.TAXSTATEMENT_CONT_METH = '');

will obviously yield the correct result.

but if you want to combine the two, the least you should have done is to write the condition separately and enclose them in brackets i.e. (t3.TAXSTATEMENT_CONT_METH = 'POST' or t3.TAXSTATEMENT_CONT_METH = '')
0
premkripalaniAuthor Commented:
%_eg_conditional_dropds(WORK.TAX_RAW);

PROC SQL;
   CREATE TABLE WORK.TAX_RAW AS
   SELECT t1.MEMBSHIP_ID,
          t2.GROUP_TYPE,
          t1.RELATIONSHIP,
          t1.TITLE,
          t1.FIRST_NAME,
          t1.SURNAME,
          t1.GENDER,
          t1.LOB,
          t3.LIVINGWELL_CONT_METH,
          t3.TAXSTATEMENT_CONT_METH
      FROM MKMASTER._MEMBERS_INFO_ AS t1, MKMASTER._MEMBSHIP_INFO_ AS t2, MKMASTER._CONTACT_INFO_ AS t3
      WHERE (t1.MEMBSHIP_ID = t2.MEMBSHIP_ID AND t2.MEMBSHIP_ID = t3.MEMBSHIP_ID AND t1.MEMBSHIP_ID = t3.MEMBSHIP_ID) AND
            (t2.GROUP_TYPE = 'RETAIL' AND t1.RELATIONSHIP = 'H' AND t1.LOB NOT = 'ALI' AND t3.LIVINGWELL_CONT_METH =
           'EMAIL') AND (t3.TAXSTATEMENT_CONT_METH = 'POST' OR '');
QUIT;

I tried this, it is still not working,
please help
0
Aloysius LowCommented:
you are not putting the conditions correctly still: t3.TAXSTATEMENT_CONT_METH = 'POST' OR ''

please change it to: (t3.TAXSTATEMENT_CONT_METH = 'POST' OR t3.TAXSTATEMENT_CONT_METH = '')

note that the condition has been broken into 2 parts.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
premkripalaniAuthor Commented:
ALL GOOD.
I was trying to run all the condition in one query. I am running query in two steps and it is all good.
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.