How to build an crosstab

[aurelienchenet]

Hello


I've got on databuilder a table with this kind of data :

id	amount	creation date	validation date	sold out date
1	10 000	12/12/2015	18/01/2015	02/02/2016
2	23 000	05/01/2016	12/01/2016
3	45 000	02/02/2016	10/02/2016	15/02/2016

I need to build a report like that :

Year	Month	Amount created	Amount validated	Amount sold out
2015	Dec	10 000	0	0
2016	Jan	23 000	33 000	0
2016	Feb	45 000	45 000	55 000

January 2016 : 33 000 € in amount validated bacause 2 lines have been validated (1 and 2)

February 2016 : 55 000 € in amount sold out brcause 2 lines have been sold out (1 and 3)

How can i do ?

Thanks

Aurélien

[Astounding]

I'm going to show you a step-by-step approach, with the warning that I might not program it this way in real life.  I might look for ways to combine some of these steps.  If you are actually processing tens of millions of records, we can look at some of the alternatives.

Prepare the data by first creating all the variables you might need.

data prepared;

set have;

year = year(creation_date);

month = month(creation_date);

if validation_date > . then validated = 'Y';

if sold_out_date > . then sold_out = 'Y';

if creation_date > . then created='Y';

run;

Then summarize as needed to get the various subtotals you require:

proc summary data=prepared nway;

class year month;

var amount;

where sold_out='Y';

output out=sum1 (keep=year month amount_sold_out) sum=amount_sold_out;

run;

proc summary data=prepared nway;

class year month;

var amount;

where created='Y';

output out=sum2 (keep=year month amount_created) sum=amount_created;

run;

 proc summary data=prepared nway;

class year month;

var amount;

where validated='Y';

output out=sum3 (keep=year month amount_validated) sum=amount_validated;

run;

Then combine all the summaries:

data want;

merge sum1 (in=in1) sum2 (in=in2) sum3 (in=in3);

by year month;

if in1=0 then amount_sold_out=0;

if in2=0 then amount_created=0;

if in3=0 then amount_validated=0;

run;

This gives you a data set that is ready (or at least close enough) for printing.  You can experiment with it, fiddle with it if you would like (for example, converting month to a 3-letter abbreviation) since this is a small data set and won't take too much time to process.