topic date diff SQL to Sas in SAS Programming

date diff SQL to Sas

mick_g — Fri, 24 Jun 2011 19:42:45 GMT

I have the code running successful in SQL. but I am having trouble converting into SAS.

SQL code

cd_1 = case when datediff (d,a.add_date, b.cd_date) ,<= 1 then 1 else 0 end

cd_2 = case when datediff (d,a.add_date, b.cd_date) ,<= 2 then 1 else 0 end

sas code

create table test as

select a.add_date

,b.cd_date

,cd_1 = case when datediff (d, a.add_date, b.cd_date) <= 1 then 1 else 0 end

,cd_2 = case when datediff (d, a.add_date, b.cd_date) <= 2 then 1 else 0 end

from data_333 a left join test_data b

on a.client = b.client and a.id = b.id;

or is it a data step;

data test;

set data_333 test_data;

cd_1 = case when datediff (d, a.add_date, b.cd_date) , <= 1 then 1 else 0 end

cd_2 = case when datediff (d, a.add_date, b.cd_date) <= 2 then 1 else 0 end

date diff SQL to Sas

Reeza — Fri, 24 Jun 2011 22:25:25 GMT

A couple of issues I can identify:

In SAS you just subtract dates, if its date times then you need to take the date part, unless you want the time in seconds or milliseconds, not sure what the smallest unit of date time is.
Also there is no CASE statement, there's a SELECT statement, but an if/then is probably what you're looking for.
Your SQL statement is merging, but your datastep code is APPENDING the dataset not merging.

I'm not 100% sure what you want but the following is a start with the following assumptions:

date is in datetime
you want difference in terms of days
Merging files by client id and id

*First need to sort both files:

proc sort data=data_333; by client id;

proc sort data=test_data; by client id;

*then need to merge and get differences;

Data Test;

MERGE data_333 (in=a) test_data (in=b);

by client id;

*get only records from data_333;

if a;

if datepart(cd_date)-datepart(add_date))<=1 then cd_1=1;

else cd_1=0;

if datepart(cd_date)-datepart(add_date)<=2 then cd_2=1;

else cd_2=0;

run;

OR SQL

create table test as

select a.add_date

,b.cd_date

,case when datepart(b.cd_date)-datepart(a.add_date) <= 1 then 1 else 0 end as cd_1

,case when datepart(b.cd_date)-datepart(a.add_date) <= 1 then 1 else 0 end as cd_2

from data_333 a left join test_data b

on a.client = b.client and a.id = b.id;

quit;

date diff SQL to Sas

Howles — Fri, 24 Jun 2011 22:26:47 GMT

SAS doesn't have the DATEDIFF function. See INTCK instead. Use DTDAY as the interval if your values are datetimes rather than dates.

PROC SQL and the DATA step are both suitable, but you can't mix and match. CASE is part of SQL and can't be used in the DATA step.

mick_g wrote:
I have the code running successful in SQL.  but I am having trouble converting into SAS.
SQL code
cd_1 = case when datediff (d,a.add_date, b.cd_date) ,<= 1 then 1 else 0 end
cd_2 = case when datediff (d,a.add_date, b.cd_date) ,<= 2 then 1 else 0 end
sas code
create table test as
select a.add_date
          ,b.cd_date
          ,cd_1 = case when datediff (d, a.add_date, b.cd_date) <= 1 then 1 else 0 end
          ,cd_2 = case when datediff (d, a.add_date, b.cd_date) <= 2 then 1 else 0 end
from data_333 a left join test_data b
on a.client = b.client and a.id = b.id;
or is it a data step;
data test;
set data_333 test_data;
cd_1 = case when datediff (d, a.add_date, b.cd_date) , <= 1 then 1 else 0 end
cd_2 = case when datediff (d, a.add_date, b.cd_date) <= 2 then 1 else 0 end

date diff SQL to Sas

Patrick — Sat, 25 Jun 2011 00:02:25 GMT

You can do it both in SQL or SAS code. Below the SAS SQL version of your code (untested).

proc sql;
create table test as

      select a.add_date
         ,b.cd_date
         ,case
            when(intck('day',a.add_date, b.cd_date)<=1) then 1
            else 0
          end as cd_1
         ,case
            when(intck('day',a.add_date, b.cd_date)<=2) then 1
            else 0
          end as cd_1
      from data_333 a left join test_data b
      on a.client = b.client and a.id = b.id;

quit;

intck(): 'day' for SAS date values, 'dtdate' for SAS datetime values

You could of course also just paste your existing SQL code into a pass-through SQL block and send it directly to the database.

If your data is in a database then use SQL before data step in order to have all the processing done in the data base.

HTH

Patrick