Desktop productivity for business analysts and programmers

Prior data carried over to next row

Accepted Solution Solved
Reply
Contributor
Posts: 33
Accepted Solution

Prior data carried over to next row

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,


Accepted Solutions
Solution
‎01-18-2018 12:51 PM
Super User
Posts: 9,594

Re: Prior data carried over to next row

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 
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Super User
Posts: 9,594

Re: Prior data carried over to next row

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 33

Re: Prior data carried over to next row

Posted in reply to KurtBremser

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.

Super User
Posts: 13,066

Re: Prior data carried over to next row

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.

Contributor
Posts: 33

Re: Prior data carried over to next row

[ Edited ]

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. 

Super User
Posts: 9,594

Re: Prior data carried over to next row


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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Solution
‎01-18-2018 12:51 PM
Super User
Posts: 9,594

Re: Prior data carried over to next row

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 
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 33

Re: Prior data carried over to next row

Posted in reply to KurtBremser

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.

Super User
Super User
Posts: 7,858

Re: Prior data carried over to next row


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. 

Contributor
Posts: 33

Re: Prior data carried over to next row

Thanks Tom, that makes sense -- I'm learning!  I will give that a try.  Appreciate your time and willingness to help.

Contributor
Posts: 33

Re: Prior data carried over to next row

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".

Super User
Posts: 9,594

Re: Prior data carried over to next row

Run my code. It uses by-group processing to achieve exactly this.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 33

Re: Prior data carried over to next row

[ Edited ]
Posted in reply to KurtBremser

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.

Super User
Posts: 9,594

Re: Prior data carried over to next row

Use first.lob instead of first.uw_team.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 33

Re: Prior data carried over to next row

Posted in reply to KurtBremser

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 17 replies
  • 274 views
  • 2 likes
  • 5 in conversation