DATA Step, Macro, Functions and more

How to embed proc sql within a data step?

Reply
Frequent Contributor
Posts: 99

How to embed proc sql within a data step?

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?

Valued Guide
Posts: 797

Re: How to embed proc sql within a data step?

[ Edited ]

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.

Frequent Contributor
Posts: 99

Re: How to embed proc sql within a data step?

[ Edited ]

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;

 

Respected Advisor
Posts: 4,644

Re: How to embed proc sql within a data step?

@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;
PG
Super User
Posts: 5,256

Re: How to embed proc sql within a data step?

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.

Data never sleeps
Frequent Contributor
Posts: 99

Re: How to embed proc sql within a data step?

Thanks for this, it may come handy later. Smiley Happy
Ask a Question
Discussion stats
  • 5 replies
  • 324 views
  • 3 likes
  • 4 in conversation