BookmarkSubscribeRSS Feed
aurelienchenet
Calcite | Level 5

Hello


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 ?

 

Thanks

 

Aurélien

1 REPLY 1
Astounding
PROC Star

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.

 

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
  • 1 reply
  • 720 views
  • 0 likes
  • 2 in conversation