QUERY: Split and backfill Data

[Ikka]

Question:

The table has material requirement from Dec2022 to Aug2023. If the previous month has Zero material requirement, split the material count accordingly among previous months.

The source and required target table as below.  Looking for SAS Base code.

Thanks in Advance!

Date	Material	Requirement				Date	Material	Requirement
01Dec2022	AA	0				01Dec2022	AA	42
01Jan2023	AA	84				01Jan2023	AA	42
01Feb2023	AA	0				01Feb2023	AA	43
01Mar2023	AA	0				01Mar2023	AA	43
01Apr2023	AA	129				01Apr2023	AA	43
01May2023	AA	0				01May2023	AA	0
01Jun2023	AA	0				01Jun2023	AA	0
01Jul2023	AA	0				01Jul2023	AA	0
01Aug2023	AA	0				01Aug2023	AA	0
01Dec2022	BBB	0		===>>>>		01Dec2022	BBB	114
01Jan2023	BBB	228			01Jan2023	BBB	114
01Feb2023	BBB	1625			01Feb2023	BBB	1625
01Mar2023	BBB	1651			01Mar2023	BBB	1651
01Apr2023	BBB	2059			01Apr2023	BBB	2059
01May2023	BBB	2075			01May2023	BBB	2075
01Jun2023	BBB	2189			01Jun2023	BBB	2189
01Jul2023	BBB	0			01Jul2023	BBB	0
01Aug2023	BBB	0			01Aug2023	BBB	0
01Dec2022	CC	0				01Dec2022	CC	22
01Jan2023	CC	0				01Jan2023	CC	22
01Feb2023	CC	0				01Feb2023	CC	22
01Mar2023	CC	88				01Mar2023	CC	22
01Apr2023	CC	0				01Apr2023	CC	13
01May2023	CC	0				01May2023	CC	13
01Jun2023	CC	0				01Jun2023	CC	13
01Jul2023	CC	0				01Jul2023	CC	13
01Aug2023	CC	65				01Aug2023	CC	13
01Dec2022	DDD	0				01Dec2022	DDD	33
01Jan2023	DDD	0				01Jan2023	DDD	33
01Feb2023	DDD	0				01Feb2023	DDD	33
01Mar2023	DDD	0				01Mar2023	DDD	33
01Apr2023	DDD	0				01Apr2023	DDD	33
01May2023	DDD	200				01May2023	DDD	33
01Jun2023	DDD	0				01Jun2023	DDD	0
01Jul2023	DDD	0				01Jul2023	DDD	0
01Aug2023	DDD	0				01Aug2023	DDD	0

[sbxkoenk]

Hello,

That's easy to do with PROC TIMEDATA (SAS/ETS).
Can you give us the source table (i.e. the left one) as a data step with datalines (cards)?

Your table cannot be copy/paste-d in my SAS editor.

When pasting SAS code in the communities

, click the "running man icon" in the toolbar on top and paste your code in the pop-up window.

Thanks,

Koen

[Ikka]

Thanks @sbxkoenk 🙂 

[SK_11 · Accepted solution]

Try this:

data have;
input Date date9. Material $ Requirement ;
format date date9.;
cards;
1-Dec-22 AA 0
1-Jan-23 AA 84
1-Feb-23 AA 0
1-Mar-23 AA 0
1-Apr-23 AA 129
1-May-23 AA 0
1-Jun-23 AA 0
1-Jul-23 AA 0
1-Aug-23 AA 0
1-Dec-22 BBB 0
1-Jan-23 BBB 228
1-Feb-23 BBB 1625
1-Mar-23 BBB 1651
1-Apr-23 BBB 2059
1-May-23 BBB 2075
1-Jun-23 BBB 2189
1-Jul-23 BBB 0
1-Aug-23 BBB 0
1-Dec-22 CC 0
1-Jan-23 CC 0
1-Feb-23 CC 0
1-Mar-23 CC 88
1-Apr-23 CC 0
1-May-23 CC 0
1-Jun-23 CC 0
1-Jul-23 CC 0
1-Aug-23 CC 65
1-Dec-22 DDD 0
1-Jan-23 DDD 0
1-Feb-23 DDD 0
1-Mar-23 DDD 0
1-Apr-23 DDD 0
1-May-23 DDD 200
1-Jun-23 DDD 0
1-Jul-23 DDD 0
1-Aug-23 DDD 0

run;

proc sort data = have out = have1;
by  Material descending date ;
run;


data have2;
set have1;
by Material   ;
if first.Material then PrevValue=Requirement ;
else if first.Material ne 1 and Requirement=0 then PrevValue=sum(PrevValue,Requirement);
else if first.Material ne 1 and Requirement ne 0 then PrevValue=Requirement;
retain PrevValue ;
run;

Proc sql;
Create table want (drop= PrevValue) as
	Select *
    , case 
		when count(PrevValue)=1  then PrevValue
		else max(PrevValue)/count(PrevValue)  
		end as NewRequirement format 12.
	from have2
	group by Material, PrevValue
	order by Material, date
;Quit;

[SK_11]

simplified part

data have2;
set have1;
by Material   ;
retain PrevValue ;

if first.Material then PrevValue = Requirement ;
 if Requirement ne 0 then PrevValue = Requirement ; 

run;

[Ikka]

Thank You @SK_11