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?
 
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
 
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
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
premkripalaniAuthor Commented:
I want to filter recordds on "Post" OR " " (Blank).
0
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.