Steffen Hvid Steffen Hvid - 1 month ago 9
SQL Question

Going from Proc SQL to Data step, when joining variables are not the same

I have the following code, i am trying to replicate it without using Proc SQL

Proc sql;
create table PA as
select a.*, b.text as county from PA a left join abnom.countyn b
on a.countycode=b.amt
;
Quit;


Normally, i would just use proc sort on the dataset and merge, by a common variable, but in this example, the countycode in a referes to amt in b, this does not work with merge. I tried making the following

proc sort data=PA;
by countycode;
run;

data tmp;
set exam.ts2;
countycode = amt;
county = tekst;
run;

proc sort data=tmp;
by countycode;
run;

data PA;
merge PA(in=a) tmp(in=b);
by countycode;
if a;
run;


And this surely merges the sets. But i don't know how to exclude and rename the variables, that is stated in the proc sql. And also i have not got access to the datasets in this problem, so i cant test my solution (although i have made test sets).

So in short, how can i replicate the proc sql, with out using the proc sql?

Best Regards,
Steffen

Answer

You are looking for dataset options keep & rename.

  proc sort data=PA;
      by countycode;
  run;

  proc sort data=exam.ts2;
      by amt;
  run;

  data PA;
      merge PA(in=a) exam.ts2(keep= amt text rename=(amt=countycode text=county));
      by countycode;
      if a;
  run;