topic sql into data step in SAS Programming

sql into data step

Geo- — Thu, 06 Sep 2018 03:49:32 GMT

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;

Re: sql into data step

PGStats — Thu, 06 Sep 2018 04:30:01 GMT

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.

Re: sql into data step

LinusH — Thu, 06 Sep 2018 06:17:09 GMT

Why?

Re: sql into data step

ballardw — Thu, 06 Sep 2018 15:04:38 GMT

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.