this maybe simple to SAS user.Someone please kindly change the sql code into data step
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;
This can be done by sorting tableA and tableB and then merging both tables by variable a. But only if variable a is unique key in at least one of the tables. Otherwise, the datastep merge operation will not be the same as a SQL join.
As an aside to the specific question, you may want to consider using actual SAS date values instead of character variables masquerading as dates. Between the date functions and date formats you can do a lot of manipulation and report grouping without adding variables with actual dates that just are not possible with character values.
Also ranges of character values such as you use such as in
where=("20150501" le batch_date le "20150519")
can be very problematic if you have unexpected (possibly erroneous ) values for the variable.
Example
data example; batch_date='201505110'; if "20150501" le batch_date le "20150519" then put "in range"; else put "out of range"; run;
Which shows that '201505110' is inside the range when using character comparisons.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.