topic Re: How to filter out subset in SAS Enterprise Guide

How to filter out subset

miss2223 — Thu, 30 Aug 2018 02:41:53 GMT

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

Re: How to filter out subset

novinosrin — Thu, 30 Aug 2018 02:47:11 GMT

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

assuming I understand your requirement

Re: How to filter out subset

miss2223 — Thu, 30 Aug 2018 03:09:31 GMT

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

Re: How to filter out subset

novinosrin — Thu, 30 Aug 2018 03:47:00 GMT

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

Re: How to filter out subset

miss2223 — Thu, 30 Aug 2018 04:12:31 GMT

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

Re: How to filter out subset

novinosrin — Thu, 30 Aug 2018 04:17:28 GMT

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

;

Re: How to filter out subset

Kurt_Bremser — Thu, 30 Aug 2018 07:28:34 GMT

@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.

Re: How to filter out subset

ballardw — Thu, 30 Aug 2018 20:22:46 GMT

@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.

Re: How to filter out subset

miss2223 — Thu, 30 Aug 2018 20:45:48 GMT

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

data want;

input var $10.;

cards;

pear

orange

;

Re: How to filter out subset

novinosrin — Thu, 30 Aug 2018 20:53:24 GMT

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

Re: How to filter out subset

miss2223 — Thu, 30 Aug 2018 21:15:31 GMT

Got it! thanks for your patience