Comparing each and every cell in two columns?

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Comparing each and every cell in two columns?

Dear all,

I have two columns with dates in them, and I want to compare each date in the first column with every other date in the second column. I have been struggling with the problem for quite sometime now. I would be grateful if you could help me with the query. A sample data set is given below:

x      date_one         y      date_two         x_new(reqd)

0.4   23/12/2003     1.2     11/04/2002      0.4*1.2*1.5

0.3   22/06/2004    1.5      26/06/2003      0.3*1.2*1.5

0.5   25/07/2005      0.75     22/11/2004    0.5*1.2*1.5*0.75

So my problem is as follows:

I want to compare each date in date_one with each and every date in date_two and modify the variable x. For example: if date_one=23/12/2003 > date_two=11/04/2002, >26/06/2003 but < 05/05/2004 then x should be adjusted as 0.4*1.2*1.5; in the second case- 22/06/2004 is > 11/04/2002, > 26/06/2003 then 0.3*1.2*1.5 and in the last case, accordingly the new x should be 0.5*1.2*1.5*0.75.

Any lead on how to proceed or any links dealing with the same issue would be of great help. Looking forward to hearing from you guys soon.

Thanks,


Accepted Solutions
Solution
‎02-19-2015 10:29 AM
Super User
Posts: 5,369

Re: Comparing each and every cell in two columns?

It's not really clear where you get the date 05/05/2004, or what the x_new logic should be, but here's the way to compare to every observation.  You may have to adjust the logic about what comparisons/adjustments to make, but this should get you close.

data want;

  set have nobs=_nobs_;

  x_new=x;

  do _i_=1 to _nobs_;

     if _i_ ne _n_ then do;

        set have (keep=y date_two rename=(y=y2 date_two=date_two2)) point=_i_;

        if date_one > date_two2 then x_new = x_new * y2;

     end;

  end;

  drop y2 date_two2;

run;

Good luck.

View solution in original post


All Replies
Solution
‎02-19-2015 10:29 AM
Super User
Posts: 5,369

Re: Comparing each and every cell in two columns?

It's not really clear where you get the date 05/05/2004, or what the x_new logic should be, but here's the way to compare to every observation.  You may have to adjust the logic about what comparisons/adjustments to make, but this should get you close.

data want;

  set have nobs=_nobs_;

  x_new=x;

  do _i_=1 to _nobs_;

     if _i_ ne _n_ then do;

        set have (keep=y date_two rename=(y=y2 date_two=date_two2)) point=_i_;

        if date_one > date_two2 then x_new = x_new * y2;

     end;

  end;

  drop y2 date_two2;

run;

Good luck.

New Contributor
Posts: 2

Re: Comparing each and every cell in two columns?

Hey Astounding,

Thanks for the reply. Thec code worked pretty well, though I had to remove "if _i_ ne _n_ then do; end:" to get the desired results.

I have a follow up question. After having operationalised this for one unique id, I want to extend it to the entire set of unique ids (by id). I know, I can always write a macro, get unique ids and put them in the macro. Is there a faster way of doing this?

Thanks in advance,

Super User
Posts: 5,369

Re: Comparing each and every cell in two columns?

It can be done.  I just hope it is something that you can study and understand, rather than just a mysterious program that seems to work.

It sounds like ID will be part of the data, your data set will be sorted BY ID, and you need to process each ID separately.

You will need to add an extra loop to determine which observations relate to the current ID.  For example:

data want;

   start = recno + 1;

   do until (last.id);

      set have;

      by id;

      recno + 1;

   end;
   finish = recno;

   do until (last.id);

      set have;

      by id;

      x_new = x;

      do _i_=start to finish;

         set have (keep=y date_two rename=(y=y2 date_two = date_two2)) point=_i_;

         if date_one > date_two2 then x_new = x_new * y2;

      end;

      output;

   end;

run;

This gives you the overall form of the program.  As always, check the results and adjust as needed.

Good luck.

Respected Advisor
Posts: 3,156

Re: Comparing each and every cell in two columns?

Here is an alternative if you have SAS 9.4,

data have;

     input x   date_one:ddmmyy10.         y date_two:ddmmyy10.;

     format date_one date_two ddmmyy10.;

     cards;

  1. 0.4   23/12/2003     1.2 11/04/2002    
  2. 0.3   22/06/2004 1.5      26/06/2003    
  3. 0.5   25/07/2005      0.75 22/11/2004  

;

data want;

     set have;

     _n_=dosubl('proc sql noprint;

           select y into :y separated by "*" from have where ' || date_one || ' >date_two ;quit;' );

     x_new=x*resolve('%sysevalf(&y)');

run;

Respected Advisor
Posts: 3,156

Re: Comparing each and every cell in two columns?

I feel the urge to add another option, and the one I provided previously does not have the practical usage due to the low efficiency. If we load the table into memory (such as Hash table), then we should be saving a lot regarding I/O, which in case is very heavy if you have a large table.

data have;

     input x   date_one:ddmmyy10.         y date_two:ddmmyy10.;

     format date_one date_two ddmmyy10.;

     cards;

  1. 0.4   23/12/2003     1.2 11/04/2002    
  2. 0.3   22/06/2004 1.5      26/06/2003    
  3. 0.5   25/07/2005      0.75 22/11/2004  

;

data want;

     if _n_=1 then

           do;

                declare hash h(dataset:'have(rename=(date_two=_two y=_y) keep=date_two y)', multidata:'y');

                declare hiter hi('h');

                h.definekey('_two');

                h.definedata(all:'y');

                h.definedone();

                call missing (_two, _y);

           end;

     set have;

     new_x=x;

     do _rc=hi.first() by 0 while (_rc=0);

           if date_one>_two then

                new_x=new_x*_y;

           _rc=hi.next();

     end;

     drop _:;

run;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 302 views
  • 7 likes
  • 3 in conversation