I am using EG 6.1 and am trying to carryover data from one row to the next row but don't know how in SAS. What I am starting with is a beginning value of zero, I then add some incoming work and subtract some outgoing work to get an end of the day value. That end of the day value becomes the beginning value for the next day. Somebody has previous mentioned either LAG() or RETAIN, but I am not sure how to use those in EG.
. The data I currently have is:
DATE TEAM GROUP CATEGORY BEGIN INCOMING OUTGOING END
6/25/2013 A 1 AUTO 1 0
7/2/2013 A 1 AUTO 1 0
7/9/2013 A 1 AUTO 0 1
7/10/2013 A 1 AUTO 2 0
7/11/2013 A 1 AUTO 2 0
7/12/2013 A 1 AUTO 0 2
What I want SAS to do (as an ongoing process step for a bigger project) is:
DATE TEAM GROUP CATEGORY BEGIN INCOMING OUTGOING END
6/25/2013 A 1 AUTO 0 1 0 1
7/2/2013 A 1 AUTO 1 1 0 2
7/9/2013 A 1 AUTO 2 0 1 1
7/10/2013 A 1 AUTO 1 2 0 3
7/11/2013 A 1 AUTO 3 2 0 5
7/12/2013 A 1 AUTO 5 0 2 3
Any help will be greatly appreciated,
So now I've made a data step out of your original example, and ran my suggested code against it. See the result, which matches your original requirement.
data have;
input date :mmddyy10. team :$1. group :$1. category :$10. incoming outgoing;
format date mmddyy10.;
cards;
6/25/2013 A 1 AUTO 1 0
7/2/2013 A 1 AUTO 1 0
7/9/2013 A 1 AUTO 0 1
7/10/2013 A 1 AUTO 2 0
7/11/2013 A 1 AUTO 2 0
7/12/2013 A 1 AUTO 0 2
;
run;
data want;
set have;
by team group category date; * date just to make sure it's correctly ordered;
retain end;
if first.category
then do;
begin = 0;
end = incoming - outgoing;
end;
else do;
begin = end;
end = end + incoming - outgoing;
end;
run;
proc print data=want noobs;
var date team group category begin incoming outgoing end; * this is just so that the variables are horizontally ordered;
run;
Result:
date team group category begin incoming outgoing end 06/25/2013 A 1 AUTO 0 1 0 1 07/02/2013 A 1 AUTO 1 1 0 2 07/09/2013 A 1 AUTO 2 0 1 1 07/10/2013 A 1 AUTO 1 2 0 3 07/11/2013 A 1 AUTO 3 2 0 5 07/12/2013 A 1 AUTO 5 0 2 3
data want;
set have;
by team group category date; * date just to make sure it's correctly ordered;
retain end;
if first.category
then do;
begin = 0;
end = incoming - outgoing;
end;
else do;
begin = end;
end = end + incoming - outgoing;
end;
run;
Untested. Please post example data in a data step, so it can easily be created by copy/paste and submit.
Thanks Kurt for your reply. I need to preface this by stating that I am not a trained programmer/coder...but have been figuring out what I can from the various resources. At this point, I have the following PROC SQL code:
CREATE TABLE WORK.QUERY_INCMNG_WRKD(label="QUERY_INCMNG_WRKD") AS
SELECT t1.DATE,
/* UW TEAM */
(IFC(t1.ASSIGNEDGROUP="TEAM1","GA/IA/KS",
IFC(t1.ASSIGNEDGROUP=" TEAM2","MN/NE",
IFC(t1.ASSIGNEDGROUP=" TEAM3","MO",
IFC(t1.ASSIGNEDGROUP=" TEAM4","IN/OH",
IFC(t1.ASSIGNEDGROUP=" TEAM5","IL",
IFC(t1.ASSIGNEDGROUP=" TEAM6","WI",
IFC(t1.ASSIGNEDGROUP=" TEAM7","AZ/ID/OR",
IFC(t1.ASSIGNEDGROUP=" TEAM8","CO/ND/SD",
IFC(t1.ASSIGNEDGROUP="TEAM9","NV/UT/WA","UNKNOWN")))))))))) LABEL="TEAM" AS 'TEAM'n,
t1.ASSIGNEDGROUP,
t1.LOB,
/* INCOMING */
(IFN(t1.STATUS="CREATED",t1.VOLUME_Sum,0)) LABEL="INCOMING" AS INCOMING,
/* WORKED */
(IFN(t1.STATUS<>"CREATED",t1.VOLUME_Sum,0)) LABEL="WORKED" AS WORKED
FROM WORK.STABSUMMARYTABLESCREATED_WORKED t1;
The data looks like this:
DATE | UW TEAM | ASSIGNEDGROUP | LOB | INCOMING | WORKED |
06/25/2013 | NV/UT/WA | TEAM9 | AUTO | 1 | 0 |
07/02/2013 | NV/UT/WA | TEAM9 | AUTO | 1 | 0 |
07/09/2013 | NV/UT/WA | TEAM9 | AUTO | 0 | 1 |
07/09/2013 | NV/UT/WA | TEAM9 | AUTO | 1 | 0 |
07/10/2013 | NV/UT/WA | TEAM9 | AUTO | 2 | 0 |
07/11/2013 | NV/UT/WA | TEAM9 | AUTO | 2 | 0 |
07/12/2013 | NV/UT/WA | TEAM9 | AUTO | 0 | 2 |
07/15/2013 | NV/UT/WA | TEAM9 | AUTO | 0 | 2 |
07/16/2013 | NV/UT/WA | TEAM9 | AUTO | 6 | 0 |
I still need to add columns for Begin and End, where the first date has a Begin value of 0 and all subsequent dates have a value equal to the End value from the prior day. The End value should always be equal to Begin + INCOMING - WORKED for the day.
I hope that this makes sense. Again, I know that my coding is probably not properly formatted, but I am just learning. Thanks for your help.
SQL in general is not well suited to processing data in order.
What role does either the assignedgroup or the team variable play in this process?
It really helps to show an outcome for a given example starting data.
Best is to provide some data we can test with. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.
Hi BallardW...thanks for your reply. Unfortunately, I am using SASEG through my corporate server and am not able to edit the autoexec.
The assignedgroup is the name of the team in the original data. There are 9 different assigned groups and each assigned group has anywhere from 1 to 3 states assigned as the name of the team. I want to break all of the data out by team and LOB in ascending date order.
@gregor1 wrote:
Hi BallardW...thanks for your reply. Unfortunately, I am using SASEG through my corporate server and am not able to edit the autoexec.
You don't need to edit the autoexec to use the dataset-to-datastep conversion macro. Just copy the text of the macro into a code window and submit that, then you can use the macro as described in the comments. Putting the macro into the autoexec is just for convenience, so that you have it at hand automatically in every later session.
So now I've made a data step out of your original example, and ran my suggested code against it. See the result, which matches your original requirement.
data have;
input date :mmddyy10. team :$1. group :$1. category :$10. incoming outgoing;
format date mmddyy10.;
cards;
6/25/2013 A 1 AUTO 1 0
7/2/2013 A 1 AUTO 1 0
7/9/2013 A 1 AUTO 0 1
7/10/2013 A 1 AUTO 2 0
7/11/2013 A 1 AUTO 2 0
7/12/2013 A 1 AUTO 0 2
;
run;
data want;
set have;
by team group category date; * date just to make sure it's correctly ordered;
retain end;
if first.category
then do;
begin = 0;
end = incoming - outgoing;
end;
else do;
begin = end;
end = end + incoming - outgoing;
end;
run;
proc print data=want noobs;
var date team group category begin incoming outgoing end; * this is just so that the variables are horizontally ordered;
run;
Result:
date team group category begin incoming outgoing end 06/25/2013 A 1 AUTO 0 1 0 1 07/02/2013 A 1 AUTO 1 1 0 2 07/09/2013 A 1 AUTO 2 0 1 1 07/10/2013 A 1 AUTO 1 2 0 3 07/11/2013 A 1 AUTO 3 2 0 5 07/12/2013 A 1 AUTO 5 0 2 3
Hi Kurt;
Thanks for your help. I am trying it with your code and will let you know how it goes. One question though, if I have thousands of lines of data (e.g., from 2013 to 2018), what do I put in between cards and run? I appreciate yours and all contributors' help.
@gregor1 wrote:
Hi Kurt;
Thanks for your help. I am trying it with your code and will let you know how it goes. One question though, if I have thousands of lines of data (e.g., from 2013 to 2018), what do I put in between cards and run? I appreciate yours and all contributors' help.
If you have thousands of lines of data you would normally store it in a file and use an INFILE statement to tell you data step where to find the lines of data instead of placing the data in-line with a CARDS (or DATALINES) statement.
But the data step in this answer was just to create some sample data so that you could see how to code the step that actually answers your question. You should already have a dataset that you want to operate on instead.
Thanks Tom, that makes sense -- I'm learning! I will give that a try. Appreciate your time and willingness to help.
I just got this to run as both of you have suggested. However, it is running all of the data as one big table with no differentiation between teams and categories. Is there a way to have the calculations restart when there is a new team and new category? For example, if the start team "AZ" and category "Auto" changes to "AZ" and "Property" respectively, I want to have a new begin starting with zero and new end (in effect, a new inventory flow for "AZ Property" independent of "AZ Auto".
Run my code. It uses by-group processing to achieve exactly this.
Here is my code (slightly different than your as I did not need the assignedgroup variable).
data WORK.QUERY_INCMNG_WRKD;
set WORK.QUERY_INCMNG_WRKD;
by uw_team LOB DATE; * date just to make sure it's correctly ordered;
retain end;
if first.uw_team
then do;
begin = 0;
end = incoming - worked;
end;
else do;
begin = end;
end = end + incoming - worked;
end;
run;
proc print data=WORK.QUERY_INCMNG_WRKD noobs;
var date uw_team LOB begin incoming worked end; * this is just so that the variables are horizontally ordered;
run;
data WORK.QUERY_INCMNG_WRKD;
set WORK.QUERY_INCMNG_WRKD;
by uw_team LOB DATE; * date just to make sure it's correctly ordered;
retain end;
if first.uw_team
then do;
begin = 0;
end = incoming - worked;
end;
else do;
begin = end;
end = end + incoming - worked;
end;
run;
proc print data=WORK.QUERY_INCMNG_WRKD noobs;
var date uw_team LOB begin incoming worked end; * this is just so that the variables are horizontally ordered;
run;
Running this does give me what I am looking for except it does not cut off and start anew for the changes in team and LOB. Not sure why.
Use first.lob instead of first.uw_team.
It worked perfectly Kurt! Again, thank you for helping this "newby" figure this out. I was able to follow the logic and will be able to apply it to other projects.
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!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.