topic Sorting?nodupkey?joining? in SAS Programming

Sorting?nodupkey?joining?

MASHASDI — Tue, 08 Oct 2019 22:48:52 GMT

I have a dataset, let's assume one customer in this dataset(we have thousands)
Date. Customer amount install
Nov2016 A 2000 0
Dec2016 A 100
Jan2017 A 100
Apr2017 A 12000 100

May2017 A 200
Jun2017 A 200
Feb2018 A 7000 200
Mar2018 A 200
Mar2019 A 5000 .
Apr2019 A .
I want:
Nov2016 A 2000 100

APR2017 A 12000 200
Mar2018 A 7000 200
Mar2019 A 5000 .
I mean, the install could start the same month as we have amnt, could start next month after amount or could be missing. I want to keep first instalment accourred after each amount for each customer. And keep them in a same row with amnt.
Thanks

Re: Sorting?nodupkey?joining?

ballardw — Tue, 08 Oct 2019 22:11:46 GMT

I think you need to explain the logic. It appears you are selecting some records but the logic is not obvious.

Also, where does the 100 for the second variable come from for Nov2016? Dec 2016, Jan 2016, the average of Dec and Jan 2016 or something else?

Also, do you need the result as a data set, something another bit of programming will use for input, or a report that people will read?

Re: Sorting?nodupkey?joining?

hashman — Wed, 09 Oct 2019 01:42:48 GMT

@MASHASDI:

A little preview can help structure the data more palatably for making use of BY processing:

data have ;                                                                                                                             
  input date :monyy7. Customer :$1. amount install ;                                                                                    
  format date yymmd7. ;                                                                                                                 
  cards ;                                                                                                                               
Nov2016  A   2000    0                                                                                                                  
Dec2016  A      .  100                                                                                                                  
Jan2017  A      .  100                                                                                                                  
Apr2017  A  12000  100                                                                                                                  
May2017  A      .  200                                                                                                                  
Jun2017  A      .  200                                                                                                                  
Feb2018  A   7000  200                                                                                                                  
Mar2018  A      .  200                                                                                                                  
Mar2019  A   5000    .                                                                                                                  
Apr2019  A      .    .                                                                                                                  
;                                                                                                                                       
run ;                                                                                                                                   
                                                                                                                                        
data vhave (drop = _:) / view = vhave ;                                                                                                 
    set have ;                                                                                                                          
    retain _a _d ;                                                                                                                      
    if amount then do ;                                                                                                                 
      _a = amount ;                                                                                                                     
      _d = date ;                                                                                                                       
    end ;                                                                                                                               
    amount = _a ;                                                                                                                       
    date   = _d ;                                                                                                                       
run ;                                                                                                                                   
                                                                                                                                        
data want ;                                                                                                                             
  set vhave ;                                                                                                                           
  by amount notsorted ;                                                                                                                 
  if last.amount ;                                                                                                                      
run ;

Kind regards

Paul D.