BookmarkSubscribeRSS Feed
Calcite | Level 5


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 ?





Opal | Level 21

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.




Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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.

Get the $99 certification deal.jpg



Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 2 in conversation