How to filter out subset

[miss2223]

Hi all,

I need help here.

I have two data set below

how can i get a result only showing Orange, Melon on the list.

I would to filter out common factors of Apple and Melon on the new list.

Thanks in advance

[novinosrin]

proc sql;
  
   select * from table1
   except
   select * from table2;

assuming I understand your requirement 

[miss2223]

Thanks for quick response.

It caused error message "180-322: Statement is not valid or it is used out
of proper order." which is underlined statement of except

[novinosrin]

Please post the sample of your datasets as plain text rather than pics and also post the output sample you want. Would be convenient if you could provide a good representative sample

[miss2223]

Thanks for your recommendation. As I am new to the communities, good to
receive some positive feedback. 🙂

Table 1 Table 2
Apple Apple
Pear Melon
Orange
Melon

[novinosrin]

should i assume

data table1;

input var $10.;

cards;

apple

pear

orange 

melon

;

data table2;

input var $10.;

cards;

apple

 melon

;

and your required output is

data want;

input var $10.;

cards;

pear

orange 

;

?

[miss2223]

Yes require output to appear "pear and orange in the list"

data want;

input var $10.;

cards;

pear

orange 

;

[novinosrin · Accepted solution]

Test:

data table1;

input var $10.;

cards;
apple
pear
orange 
melon
;

 

data table2;

input var $10.;

cards;
apple
 melon
;


proc sql;
  create table want as
   select * from table1
   except
   select * from table2;
   quit;

proc print nobs;run;

RESULTS:

SAS Output

var
orange
pear

[miss2223]

Got it! thanks for your patience

[Kurt_Bremser]

---

@miss2223 wrote:
Thanks for quick response.

It caused error message "180-322: Statement is not valid or it is used out
of proper order." which is underlined statement of except

---

You alread got lesson #1 for successful questions from @novinosrin (post example data in data steps with datalines)

Lesson #2:

When encountering an ERROR (or another message you do not understand), always post the whole log of the failing step into a window opened with the {i} button. The message alone does not tell us much, its positioning in relation to the code (and the code itself, of course) is important. Since the main posting window scrambles the formatting (by removing blanks), using the {i} is mandatory.

[ballardw]

---

@miss2223 wrote:
Thanks for quick response.

It caused error message "180-322: Statement is not valid or it is used out
of proper order." which is underlined statement of except

---

Sounds like you may have a ; at the end of the first part of:  select * from table ;

If so remove it. SQL script bits use one ; to end the whole statement which can have many parts.