03-01-2016 06:40 AM
I have the following db:
Status client (from a to n) -character
Market (multiples) -character
id_client (multiple codes) -character
observation date (date from 1 to 3) -date
amount - numeric
I need to create a DB where I can follow each client over time, i.e. a DB with the following variables
market, Id client, sum(amount at date1), sum(amount) at date2, sum(amount) at date 3, status of client at date1, status of client at date2, staus of client at date3.
Could you suggest me a code that could work?
Thank you very much
03-01-2016 07:16 AM
sum amount at date (n), meaning accumulated?
Sounds like report to me, not a DB (or table/data set).
Even if cubes aren't hot anymore, they can handle this kind summaries quite well, and allows for some kind of flexibility in the reporting.
03-01-2016 08:37 AM
Well, the output should be a dataset not a report.
Yes by sum amount at date (n) I mean accumulated amount at date n for each client, in each status in each market.
03-01-2016 10:09 AM
Your update includes important information ... that each client/market/status gets totaled separately. Here's one way:
proc sort data=have;
by id_client market status date;
by id_client market status;
if first.status then tot_amount = amount;
else tot_amount + amount;
I agree that this looks more like a report than a data set, but that's not for me to judge. This should get you what you are asking for.