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

0
votes

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

1
sqlmergesas

1 Answers

1
votes

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;