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
A couple of issues I can identify:
I'm not 100% sure what you want but the following is a start with the following assumptions:
*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;
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
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
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.