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 |
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;
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
Thanks @sbxkoenk 🙂
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;
simplified part
data have2;
set have1;
by Material ;
retain PrevValue ;
if first.Material then PrevValue = Requirement ;
if Requirement ne 0 then PrevValue = Requirement ;
run;
Thank You @SK_11
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.