topic sas sql into data step in SAS Programming

sas sql into data step

Geo- — Sun, 16 Sep 2018 01:31:51 GMT

Could someone familiar with sas change sql into data step.thank you for your attention

proc sql noprint;
create table temp as
select A.a,A.b,A.c,A.d,
put(datepart(A.e),YYMMDDN8.) as e,
case when A.f is null then calculated e
else A.f end as f,
put(datepart(A.g),YYMMDDN8.) as g,
put(datepart(A.h),YYMMDDN8.) as h,
B.city,
B.channel
from tableA(where=("20150501" le batch_date le "20150519")) A
left join (select distinct a,f,city,channel from tableB) B
on A.a=B.a
order by 1;
quit;

Re: sas sql into data step

Jagadishkatam — Sun, 16 Sep 2018 02:40:18 GMT

proc sort data=tablea;
by a;
where "20150501" le batch_date le "20150519";
run;

proc sort data=tableb;
by a;
run;

data temp;
merge tablea(in=ina) tableb(in=inb);
by a;
if ina;
run;

Re: sas sql into data step

Patrick — Sun, 16 Sep 2018 03:42:27 GMT

As a variant to what @Jagadishkatam posted.

You will need to add a least a NODUPKEY to the 2nd sort in order to replicate the DISTINCT in the SQL sub-select.

You will also need to control from which source data set same named columns get read.

proc sort data=tablea out=_tablea;
  by a;
  where "20150501" le batch_date le "20150519";
run;

proc sort data=tableb(keep=a f city channel) out=_tableb nodupkey;
  by a f city channel;
run;

data want(drop=_:);
  merge 
    _tablea(
      in=ina 
      keep=a b c d e f g h
      rename=(e=_e g=_g h=_h)
      ) 
    _tableb(
      in=inb
      keep=a city channel
      )
    ;
  by a;
  if ina;

  length e g h $8;
  e=put(datepart(_e),YYMMDDN8.);
  g=put(datepart(_g),YYMMDDN8.);
  h=put(datepart(_h),YYMMDDN8.);

  if missing(f) then f=e;

run;

N.B: You haven't provided sample data so above code is not tested.