topic Re: IN operator in proc sql in SAS Programming

IN operator in proc sql

Ronein — Sun, 05 Aug 2018 08:49:26 GMT

Hello

In proc sql i want to use in operator.

What is the correct way?

If there are some items ,should I use comma between them or also a space can work?

I run this example and both method worked

Data tbl;
input ID  branch;
cards;
1 100
2 100
3 200
4 300
5 400
6 500
7 500
8 500
9 500
10 500
;
run;

PROC SQL;
	create table outcome as
	select *	   
	from tbl
	where branch in (100, 200, 300, 400, 500)
;
QUIT;

PROC SQL;
	create table outcome as
	select *	   
	from tbl
	where branch in (100  200  300  400  500)
;
QUIT;

Re: IN operator in proc sql

Kurt_Bremser — Sun, 05 Aug 2018 08:59:56 GMT

Both methods are equal. I personally prefer commas in "in"-lists because I rarely use SQL and commas are needed in the when() conditions of select() blocks in data steps, where the in-lists often end up.

Re: IN operator in proc sql

PeterClemmensen — Sun, 05 Aug 2018 10:14:03 GMT

They generate identical results, but IMO it is bad programming practice to omit the commas.

Re: IN operator in proc sql

Tom — Sun, 05 Aug 2018 17:19:19 GMT

That is one of the nicest features of SAS code, especially if you need to create macros that take in lists of values as parameters.

Commas are a pain to pass into macro calls because they are used to separate parameters.

%macro subset(in,out,var,list);
data &out;
 set &in;
 where &var in (&list);
run;
%mend subset;

proc sql noprint;
  select quote(trim(name)) into :mylist separated by ' '
    from sashelp.class
    where sex='F'
  ;
quit;

%subset(in=sashelp.class,out=females,var=name,list=&mylist);