Adding a specific column in various tables into 1 table without using query builder

[RG_PW]

Hi all,

Ok so im new on here. My SAS coding skills are moderate at best.

I have a lot of tables (monthly data) and there is a specific column out of each months data that I want to all add to a new table (but dont want the table appended and dont want to use query builder).

Lets paint the picture of the tables.

I have a company, a product, volumes sold, value sold in each of the monthly tables.

Now I want to create a table where I have all the company, products information, now I want to join the monthly volumes and values sold to the company and product data.

I dont want to use append table as I would like the monthly data in columns next to one another. And I dont want to use query builder as we want users of the SAS code to manually define the start month and end month (and this will obviously be an issue with a query builder where the months has to be defined in the code). Same with an append table statement.

So when joining the monthly sales volumes and sales values I would like to rename it to sales201601, volumes201601 and next months data is then sales201602, volumes 201602.

Currently in the monthly data these columns are just called sales, volumes.

Is there a Do statement or a macro that can be used to achieve this?

Regards

RG_PW

[PGStats]

Something like:

data want;
merge
    data201601(rename=(sales=sales201601 volumes=volumes201601))    
    data201602(rename=(sales=sales201602 volumes=volumes201602));
by company product;
run;

(untested)

[RG_PW]

Seems easy enough.

How do I get around the fact that I want user to select start and end date.

I.e 

%let start=201601;

%let end= 201712;

Then how do I ensure all months between &start. and &end. is merged into this table?

Can code a bit in SAS but do until loops has always gotten the better of me

[PGStats]

A rather simple approach would avoid the complexities of macros:

filename tempCode temp;

data _null_;
file tempCode;
..... write dataset names and options to tempCode with PUT statements...
run;

data want;
merge
%include tempCode;;
by company product;
run;

[RG_PW]

So this is the query builder I would like to "automate" by it adding every month between the start and end date set by user to Left join certain columns to the beginning month and copying over certain columns from the various tables.

PROC SQL;
CREATE TABLE WORK.Joined_QUANTITY AS
SELECT t1.company,
t1.product,
t1.Unit_201702,
t1.Quantity_201702,
t2.Quantity_201703,
t3.Quantity_201704
FROM QUANTITY_201702 t1

LEFT JOIN TRADEED.QUANTITY_201703 t2 ON (t1.company= t2.company) AND (t1.product, = t2.product,) AND (t1.Unit_201702 = t2.Unit_201703)

LEFT JOIN TRADEED.QUANTITY_201704 t3 ON (t1.company =t3.company) AND (t1.product = t3.product) AND (t1.Unit_201702 = t3.Unit_201704);
QUIT;

Basically I want to create a time series in which every months values are added, based on the join of company, then product then units (litres, kilograms, ounces etc).

So the table will look something like this:

Company, Product, Unit(of the beginning month), Value (beginning month), Unit (of 2nd month), value (of 2nd month),  Unit (of 3rd month), value (of 3rd month).

But all I want the user to do is select the start date and end date. Then want SAS to automatically add the value and units of each month to a table by company, product, unit.

Doable? Sure.

Can I code it? Absolutely not. lol!