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?

Trusted Advisor
Posts: 1,022

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

[ Edited ]
Posted in reply to afiqcjohari

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,927

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

Posted in reply to afiqcjohari

@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,431

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
  • 596 views
  • 3 likes
  • 4 in conversation