topic Re: How to embed proc sql within a data step? in SAS Programming

How to embed proc sql within a data step?

afiqcjohari — Wed, 15 Mar 2017 01:23:15 GMT

data want;
set have1;
where var in (
   proc sql; 
      select distinct var from have2;
  quit;);

Or how to write the proc sql part in pure data step?

mkeintz — Wed, 15 Mar 2017 01:29:18 GMT

Your code would not work. I think you want all the observations in HAVE1 with a value for VAR that also occurs in HAVE2:

data want;

set have1;

if _n_=1 then do;

declare hash h (dataset:'have2 (keep=var)');

h.definekey('var');

h.definedone();

end;

rc=h.find();

if rc=0;

run;

Or if you don't want implement hash objects, you can use a couple of proc sorts, followed by a merge:

proc sort data=have2 (keep=var) out=need2 nodupkey;

by var;

run;

proc sort data=have1;

by var;

run;

data want;

merge have1 have2 (in=in2);

by var;

if in2;

run;

The latter requires for more input/output.

afiqcjohari — Wed, 15 Mar 2017 01:37:53 GMT

The data step looks more complicated.

I think a full proc sql is the simplest and readable.

proc sql;
 create table want as
 select * from have1
 where var in (select distinct var in have2);
quit;

PGStats — Wed, 15 Mar 2017 03:22:15 GMT

@afiqcjohari, yes, SQL is simpler in this case, but you need the proper syntax:

proc sql;
 create table want as
 select * from have1
 where var in (select var from have2);
quit;

LinusH — Wed, 15 Mar 2017 08:38:39 GMT

Agree with other that stick to SQL for the whole query is the preferred technique.

But if there's another situation, and really would benefit from nesting SQL code, take a look at DS2.

afiqcjohari — Wed, 15 Mar 2017 09:17:18 GMT

Thanks for this, it may come handy later. 🙂