DATA Step, Macro, Functions and more

find the minimum value

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

find the minimum value

Good evening,

 

I have following transaction data. one transaction (id) can have max 4 listing agents and 4 selling agents.

I want to generate a variable named min_date_la1 to illustrate the oldest transaction the listing_1 undertook (as listing_1, listing_2, listing_3 or listing_4).

In other words, I want to find the oldest date_sign by comparing the listing_1 within the listing_1 column and with listing_2 column. For example listing agent A involved in total 5 transactions, (ID=1,4,5,7,10), among them, two was being the listing_1 and other three was being the listing_2. I want to compare the date_sign of those five transactions and generate a new variable called min_date_la1 = min(date_sign). In this case the min_date_la1 for id=1 and id=5 both are 2008-05-11 00:00:00.

Can anyone help on the code? I really appreciate.

Thank you so much

obs    id     listing_1  listing_2  selling_1 selling_2       status             date_sign                      la1_no_la1_5     la1_no_la2_5

1        1           A              B             C            D              sold               2012-01-23 00:00:12            1                        2

2        2           C             .               D             .               sold              2007-05-01  00:01:15            0                        0

3        3           E              F             A             C              sold              2013-03-05  08:21:05            1                        0

4        4           B              A              .              .              expired         2011-01-15 05:21:00              0                       0

5        5            A             C             B             F               sold              2008-05-11 00:00:00             0                       0

6        6           E               .             D             .                sold               2011-07-15 00:23:15            0                        0

7        7           C              A            A             .                 sold               2012-11-26 00:15:00             1                       1

8        8           C               .             C              F             sold               2007-12-15  00:17:25            1                        0

9        9           F              B             B              C              sold               2014-12-01 00:00:00            0                       1

10      10          D             A              .               .              expired         2011-12-14 00:15:25              0                        0

 


Accepted Solutions
Solution
a week ago
Frequent Contributor
Posts: 112

Re: find the minimum value

[ Edited ]

@freda:

 

If i understand your issue correctly (and it's a big IF), you can try the following:

data trans ;                                                                  
  input id (listing_1 listing_2) (:$1.) date_sign:yymmdd10. ;                 
  format date_sign yymmdd10. ;                                                
  cards ;                                                                     
  1  A  B  2012-01-23                                                         
  2  C  .  2007-05-01                                                         
  3  E  F  2013-03-05                                                         
  4  B  A  2011-01-15                                                         
  5  A  C  2008-05-11                                                         
  6  E  .  2011-07-15                                                         
  7  C  A  2012-11-26                                                         
  8  C  .  2007-12-15                                                         
  9  F  B  2014-12-01                                                         
 10  D  A  2011-12-14                                                         
run ;                                                                         
                                                                              
data agent (keep = agent date_sign) / view = agent ;                          
  set trans (keep = listing: date_sign) ;                                                                 
  agent = listing_1 ; output ;                                                
  agent = listing_2 ; output ;                                                
run ;                                                                         
                                                                              
proc sql ;                                                                    
  create table want as                                                        
  select t.*                                                                  
       , a.min_date_sign format=yymmdd10.                                     
  from   trans t                                                              
  left join                                                                   
         (select agent, min (date_sign) as min_date_sign from agent group 1) a
  on     t.listing_1 = a.agent                                                
  order  t.id                                                                 
  ;                                                                           
quit ;                                                                        

HTH

Paul D.

View solution in original post


All Replies
Solution
a week ago
Frequent Contributor
Posts: 112

Re: find the minimum value

[ Edited ]

@freda:

 

If i understand your issue correctly (and it's a big IF), you can try the following:

data trans ;                                                                  
  input id (listing_1 listing_2) (:$1.) date_sign:yymmdd10. ;                 
  format date_sign yymmdd10. ;                                                
  cards ;                                                                     
  1  A  B  2012-01-23                                                         
  2  C  .  2007-05-01                                                         
  3  E  F  2013-03-05                                                         
  4  B  A  2011-01-15                                                         
  5  A  C  2008-05-11                                                         
  6  E  .  2011-07-15                                                         
  7  C  A  2012-11-26                                                         
  8  C  .  2007-12-15                                                         
  9  F  B  2014-12-01                                                         
 10  D  A  2011-12-14                                                         
run ;                                                                         
                                                                              
data agent (keep = agent date_sign) / view = agent ;                          
  set trans (keep = listing: date_sign) ;                                                                 
  agent = listing_1 ; output ;                                                
  agent = listing_2 ; output ;                                                
run ;                                                                         
                                                                              
proc sql ;                                                                    
  create table want as                                                        
  select t.*                                                                  
       , a.min_date_sign format=yymmdd10.                                     
  from   trans t                                                              
  left join                                                                   
         (select agent, min (date_sign) as min_date_sign from agent group 1) a
  on     t.listing_1 = a.agent                                                
  order  t.id                                                                 
  ;                                                                           
quit ;                                                                        

HTH

Paul D.

Contributor
Posts: 20

Re: find the minimum value

Thank you. It works very well. I appreciate!

Freda
Frequent Contributor
Posts: 112

Re: find the minimum value

@freda:

 

On second thought, if you don't like creating the DATA step view beforehand and would like to handle everything in a single SQL step, you can replace the code I've posted earlier with the following, where a union is used in lieu of the view:

data trans ;                                                    
  input id (listing_1 listing_2) (:$1.) date_sign:yymmdd10. ;   
  format date_sign yymmdd10. ;                                  
  cards ;                                                       
  1  A  B  2012-01-23                                           
  2  C  .  2007-05-01                                           
  3  E  F  2013-03-05                                           
  4  B  A  2011-01-15                                           
  5  A  C  2008-05-11                                           
  6  E  .  2011-07-15                                           
  7  C  A  2012-11-26                                           
  8  C  .  2007-12-15                                           
  9  F  B  2014-12-01                                           
 10  D  A  2011-12-14                                           
run ;                                                           
                                                                
proc sql ;                                                      
  create table want as                                          
  select t.*                                                    
       , a.min_date_sign format=yymmdd10.                       
  from   trans t                                                
  left join                                                     
         (select agent, min (date_sign) as min_date_sign        
          from (select listing_1 as agent, date_sign from trans 
                union all                                       
                select listing_2 as agent, date_sign from trans)
          group 1) a                                            
  on     t.listing_1 = a.agent                                  
  order  t.id                                                   
  ;                                                             
quit ;                                                          

Yet again, if you don't mind using the hash object, want to read TRANS twice instead of thrice, and eliminate behind-the-scenes SQL sort, this will run more efficiently:

data want_hash (drop = agent) ;                          
  if _n_ = 1 then do ;                                   
    dcl hash h () ;                                      
    h.defineKey ("agent") ;                              
    h.defineData ("agent", "min_date_sign") ;            
    h.defineDone () ;                                    
    do until (z) ;                                       
      set trans (keep = id listing: date_sign) end = z ; 
      array list listing: ;                              
      do over list ;                                     
        agent = list ;                                   
        if h.find() ne 0 then min_date_sign = date_sign ;
        else min_date_sign = min_date_sign >< date_sign ;
        h.replace() ;                                    
      end ;                                              
    end ;                                                
  end ;                                                  
  set trans ;                                            
  _n_ = h.find(key:listing_1) ;                          
  format min_date_sign yymmdd10. ;                       
run ;                                                    

Note that ID is kept in the first read of TRANS only for the sake of replicating the input order of the variables in the output, so that ID would come first.

 

Paul D. 

☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 86 views
  • 0 likes
  • 2 in conversation