BookmarkSubscribeRSS Feed
alessandra9010
Calcite | Level 5

Hi all,

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

 

AA

3 REPLIES 3
LinusH
Tourmaline | Level 20

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.

Data never sleeps
alessandra9010
Calcite | Level 5

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.

Astounding
PROC Star

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;

run;

 

data want;

set have;

by id_client market status;

if first.status then tot_amount = amount;

else tot_amount + amount;

run;

 

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.

 

Good luck.

 

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
  • 860 views
  • 0 likes
  • 3 in conversation