topic Array help with inventory tracking table in SAS Procedures

Array help with inventory tracking table

dane77221 — Tue, 07 Aug 2018 16:14:09 GMT

I'm trying to create a table that will show the demand for an item until it hits below the current inventory in which case the columns would show the total negative demand. The inputs I am receiving are never in the same format that is why I have to use the proc contents function. Please see my table example below (this is a very simplified version) and the code I've got so far.

DATA HAVE;
infile datalines delimiter=',';
INPUT 
ITEM $ INVENTORY MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY SATURDAY;
DATALINES;
1A,57,8,.,10,7,38,.
1B,.,.,45,11,.,76,96
1C,49,.,.,44,96,53,49
1D,80,.,85,14,55,.,2
1E,29,23,34,61,.,12,.

;
RUN;

PROC CONTENTS
DATA = HAVE
OUT = CONT;
RUN;

PROC SQL;
	CREATE TABLE CONT1 AS
	SELECT
	NAME AS COL,
	VARNUM AS COL_NUM
FROM CONT 
WHERE VARNUM > 1
ORDER BY VARNUM;
QUIT;


 proc sql noprint;
 select  COL
 into :varnames separated by ' '
 from CONT1
order by COL_NUM;
 quit;

%PUT NOTE: &varnames;

data want;
    set HAVE;
    array x &varnames;
    do i= 2 to dim(x);
	if x(i)=. then x(i) = x(i-1);
 	else x(i)= x(i-1) - x(i);
    end;
    drop i;
run;

Re: Array help with inventory tracking table

PaigeMiller — Tue, 07 Aug 2018 16:49:33 GMT

You need to have a variable named cumulative_inventory, which for Item 1A starts at 57.

Then you subtract the demand from cumulative Inventory.

If cumulative inventory is still positive, then you leave the value of the demand in the output table as a positive number; if cumulative Inventory becomes negative, then the value in the output table becomes the negative number of cumulative inventory. (This seems like a very strange and somewhat misleading way to table this information, but that seems to be what you have asked for).

cumulative_inventory=inventory;

do i=1 to dim(x);
    cumulative_inventory = cumulative_inventory - x(i);
    if cumulative_inventory<0 then x(i) = cumulative_inventory;
end;

Re: Array help with inventory tracking table

dane77221 — Tue, 07 Aug 2018 17:02:31 GMT

Thank you for looking into this!

Below is the output I got by trying to implement what you are suggesting.

Maybe i'm doing it wrong but also I need to show a blank cell when there is no demand

any Ideas on how I could do that?

Please Advise

code:

DATA HAVE;
infile datalines delimiter=',';
INPUT 
ITEM $ INVENTORY CUM_INV MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY SATURDAY;
DATALINES;
1A,57,57,8,.,10,7,38,.
1B,.,.,.,45,11,.,76,96
1C,49,49,.,.,44,96,53,49
1D,80,80,.,85,14,55,.,2
1E,29,29,23,34,61,.,12,.

;
RUN;

PROC CONTENTS
DATA = HAVE
OUT = CONT;
RUN;

PROC SQL;
	CREATE TABLE CONT1 AS
	SELECT
	NAME AS COL,
	VARNUM AS COL_NUM
FROM CONT 
WHERE VARNUM > 2
ORDER BY VARNUM;
QUIT;


 proc sql noprint;
 select  COL
 into :varnames separated by ' '
 from CONT1
order by COL_NUM;
 quit;

%PUT NOTE: &varnames;

data want;
    set HAVE;
    array x &varnames;
   do i= 2 to dim(x);
	if x(i)=. then x(i) = x(i-1);
 	else x(i)= x(i-1) - x(i);
    end;
    drop i;
run;
DATA WANT2;
	SET want;
array x &varnames;
do i=1 to dim(x);
    CUM_INV = CUM_INV - x(i);
    if CUM_INV < 0 then x(i) = CUM_INV;
end;
DROP i;
run;

Re: Array help with inventory tracking table

PaigeMiller — Tue, 07 Aug 2018 17:53:09 GMT

The first line of code I provided seems to be missing from your code.

Also you shouldn't have DATA WANT; followed by DATA WANT2; Just do the loop that is now in DATA WANT2; plus the one line you missed.

Re: Array help with inventory tracking table

PaigeMiller — Tue, 07 Aug 2018 17:56:03 GMT

Minor correction to my ocde

cumulative_inventory=inventory;

do i=1 to dim(x);
    cumulative_inventory = sum(cumulative_inventory, -x(i));
    if cumulative_inventory<0 then x(i) = cumulative_inventory;
end;