Traditional web-based reporting with SAS BI tools

How to build an crosstab

Posts: 1

How to build an crosstab

[ Edited ]


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

idamountcreation datevalidation datesold out date
110 00012/12/201518/01/201502/02/2016
223 00005/01/201612/01/2016 
345 00002/02/201610/02/201615/02/2016

I need to build a report like that :

YearMonthAmount createdAmount validatedAmount sold out
2015Dec10 00000
2016Jan23 00033 0000
2016Feb45 00045 00055 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 ?





Super User
Posts: 5,371

Re: How to build an crosstab

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';



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;


proc summary data=prepared nway;

class year month;

var amount;

where created='Y';

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


 proc summary data=prepared nway;

class year month;

var amount;

where validated='Y';

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



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;



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.


Ask a Question
Discussion stats
  • 1 reply
  • 2 in conversation