BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
freda
Fluorite | Level 6

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
hashman
Ammonite | Level 13

@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

3 REPLIES 3
hashman
Ammonite | Level 13

@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.

freda
Fluorite | Level 6
Thank you. It works very well. I appreciate!

Freda
hashman
Ammonite | Level 13

@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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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