topic Re: QUERY: Split and backfill Data in SAS Programming

QUERY: Split and backfill Data

Ikka — Mon, 19 Dec 2022 12:32:28 GMT

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

Re: QUERY: Split and backfill Data

sbxkoenk — Mon, 19 Dec 2022 21:50:37 GMT

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

Re: QUERY: Split and backfill Data

SK_11 — Wed, 21 Dec 2022 01:39:23 GMT

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;

Re: QUERY: Split and backfill Data

SK_11 — Wed, 21 Dec 2022 02:05:14 GMT

simplified part

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

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

run;

Re: QUERY: Split and backfill Data

Ikka — Wed, 21 Dec 2022 04:03:35 GMT

Thanks @sbxkoenk 🙂

Re: QUERY: Split and backfill Data

Ikka — Wed, 21 Dec 2022 04:04:33 GMT

Thank You @SK_11