BookmarkSubscribeRSS Feed
mick_g
Calcite | Level 5

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

3 REPLIES 3
Reeza
Super User

A couple of issues I can identify:

  1. 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.
  2. Also there is no CASE statement, there's a SELECT statement, but an if/then is probably what you're looking for.
  3. 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;

Howles
Quartz | Level 8

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

Patrick
Opal | Level 21

Hi

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 39112 views
  • 3 likes
  • 4 in conversation