Hi, dear all,
Thank you first for helping me with this problem.
I want to create a new variable calculating the following equation:
X=sum {min(dt_sold(i), dt_sold(j)) - max(dt_cre(i), dt_sold(j))} if dt_sold(i) or dt_sold(j) is missing, use dt_exp(i) or dt_exp(j) instead.
i and j are the unique id number of each transaction, and i does not equal to j.
I expect to run i from 1 to N and j from 1 to N.
transaction j has to satisfy other condition such as d_fsa (i) = d_fsa (j).
proc sql;
create table table1 as
select unique la1.*, sum(min(la1.date_sold, past1.date_sold)-max(la1.date_cre, past1.date_cre)) as o1
from la1
left join
la1 past1
on (la1.d_fsa=past1.d_fsa)
group by 1
order by 1 ;
quit;
I try my best to write the above code, but I know it is wrong.
Can anyone help me with the code.
Thank you so much
Best,
Freda
If I understand the problem, it seems like SQL is the wrong tool to do this.
It seems like you need to loop over i going 1 to N and j going 1 to N without i=j
Something like this, assuming N=20:
data want;
set have;
array dt_sold dt_sold1-dt_sold20;
array dt_cre dt_cre1-dt_cre20;
array dt_exp dt_exp1-dt_exp20;
sum=0;
do i=1 to N
do j=1 to N;
if i^=j then do;
/* your formula goes here */
/* this is just an example: */
sum=sum + min(dt_sold(i),dt_sold(j)) -
max(dt_cre(i),dt_sold(j));
end;
end;
end;
run;
Show us a portion of your data so we can see what it looks like so we can figure out how to write the code.
For your Excel data, show what you would expect as the output.
@freda wrote:
Hi, dear all,
Thank you first for helping me with this problem.
I want to create a new variable calculating the following equation:
X=sum {min(dt_sold(i), dt_sold(j)) - max(dt_cre(i), dt_sold(j))} if dt_sold(i) or dt_sold(j) is missing, use dt_exp(i) or dt_exp(j) instead.
i and j are the unique id number of each transaction, and i does not equal to j.
I expect to run i from 1 to N and j from 1 to N.
transaction j has to satisfy other condition such as d_fsa (i) = d_fsa (j).
proc sql; create table table1 as select unique la1.*, sum(min(la1.date_sold, past1.date_sold)-max(la1.date_cre, past1.date_cre)) as o1 from la1 left join la1 past1 on (la1.d_fsa=past1.d_fsa) group by 1 order by 1 ; quit;
I try my best to write the above code, but I know it is wrong.
Can anyone help me with the code.
Thank you so much
Best,
Freda
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!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.