topic Re: Can anyone help me with this programming problem in New SAS User

Can anyone help me with this programming problem

freda — Sat, 12 Jan 2019 23:37:49 GMT

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

Re: Can anyone help me with this programming problem

PaigeMiller — Sun, 13 Jan 2019 01:18:41 GMT

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;

Re: Can anyone help me with this programming problem

freda — Sun, 13 Jan 2019 03:30:37 GMT

Hi, my variable dt_sold is only one variable with 10 observations. with your code, the sas generate 20 variables from dt_sold1 to dt_sold20. I want to compare cross observations not cross variables. i and j are the ith observation and jth observation.

Thank you

Best,

Yanting

Re: Can anyone help me with this programming problem

PaigeMiller — Sun, 13 Jan 2019 17:15:05 GMT

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.

Re: Can anyone help me with this programming problem

Reeza — Sun, 13 Jan 2019 19:04:21 GMT

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