Hello All,
I have a Product Sales Table currently and I need to load new values(column) called Annual Sales Projectioninto that table every 3 months. Below are the source tables and the desired table.
Product Sales Table:
Date Product Sales
01/01/2017 Pen 100
01/01/2017 Book 100
01/01/2017 Pencil 100
02/01/2017 Pen 400
02/01/2017 Book 200
02/01/2017 Pencil 400
03/01/2017 Pen 200
03/01/2017 Book 100
03/01/2017 Pencil 200
04/01/2017 Pen 100
04/01/2017 Book 400
04/01/2017 Pencil 100
05/01/2017 Pen 400
05/01/2017 Book 400
05/01/2017 Pencil 400
06/01/2017 Pen 400
06/01/2017 Book 400
06/01/2017 Pencil 400
Annual Sales Projection:
Quarter 1: 01/01/2017
02/01/2017
03/01/2017
Pen = 600
Book = 502
Pencil = 400
Quarter 2: 04/01/2017
05/01/2017
06/01/2017
Pen = 3000
Book = 2000
Pencil = 5000
Desired table:
Date Product Sales Annual Sales Projection
01/01/2017 Pen 100 600
01/01/2017 Book 100 502
01/01/2017 Pencil 100 400
02/01/2017 Pen 400 600
02/01/2017 Book 200 502
02/01/2017 Pencil 400 400
03/01/2017 Pen 200 600
03/01/2017 Book 100 502
03/01/2017 Pencil 200 400
04/01/2017 Pen 100 3000
04/01/2017 Book 400 2000
04/01/2017 Pencil 100 5000
05/01/2017 Pen 400 3000
05/01/2017 Book 400 2000
05/01/2017 Pencil 400 5000
06/01/2017 Pen 400 3000
06/01/2017 Book 400 2000
06/01/2017 Pencil 400 5000
Basically, Annual Sales Projection changes every 3 months and I have to load those static values into the table.
Any help will be greatly appreciated.
Thank you.
It seems that you can either merge data - if both are sorted by date and product or
do proc append of new quarter to the old/accumulated dataset and then sort by date and product.
Give the details of
Annual Sales Projection table in a table format like the first table and not like notes. I am too lazy to type even for my own sake let alone others. Please
Quarter 1: 01/01/2017
02/01/2017
03/01/2017
Pen = 600
Book = 502
Pencil = 400
Quarter 2: 04/01/2017
05/01/2017
06/01/2017
Pen = 3000
Book = 2000
Pencil = 5000
@ndee Untested because you didn't provide the 2nd table AnnualSalesProjection in table format to easy copy paste
data want;
if _N_ = 1 then do;
if 0 then set AnnualSalesProjection;
declare hash h(dataset:'AnnualSalesProjection',multidata:'y');
h.defineKey('quarter',);
h.defineData(all:'y');
h.defineDone();
end;
set ProductSalesTable;
array t(*) pen--pencil ;
if find(key:date)=0 then do;
do _n_=1 to dim(t);
if vname(t(_n_))=product then do;AnnualSalesProjection=t(_n_);leave;end;
end;
end;
else call missing(AnnualSalesProjection);
keep Date Product Sales AnnualSalesProjection;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.