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
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?
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.