topic Re: Load Values to a table in SAS Programming

Load Values to a table

ndee — Tue, 30 Jan 2018 18:40:05 GMT

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.

Re: Load Values to a table

Shmuel — Tue, 30 Jan 2018 19:17:55 GMT

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.

Re: Load Values to a table

novinosrin — Tue, 30 Jan 2018 19:27:05 GMT

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

Re: Load Values to a table

novinosrin — Tue, 30 Jan 2018 20:05:53 GMT

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