topic Re: Prior data carried over to next row in SAS Enterprise Guide

Prior data carried over to next row

gregor1 — Tue, 16 Jan 2018 15:16:33 GMT

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,

Re: Prior data carried over to next row

Kurt_Bremser — Tue, 16 Jan 2018 15:25:26 GMT

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.

Re: Prior data carried over to next row

RW9 — Tue, 16 Jan 2018 15:25:52 GMT

Is there not a code block in EG? I haven't used it in years, but you can create items and link them together, and one of those items is SAS code files. Then within the SAS code file you write a datastep to process your data as you want. And yes, retain is the method you want to use.

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000214163.htm

Re: Prior data carried over to next row

gregor1 — Tue, 16 Jan 2018 18:22:28 GMT

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.

Re: Prior data carried over to next row

gregor1 — Tue, 16 Jan 2018 18:28:19 GMT

Thanks RW9 for your reply...I need to think about that and how to proceed. I am not an experienced coder so will need to read up on you comments. Have been trying to figure it out for a while, so your comments give me another lead.

Re: Prior data carried over to next row

ballardw — Tue, 16 Jan 2018 20:13:50 GMT

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-data-AKA-generate/ta-p/258712 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.

Re: Prior data carried over to next row

gregor1 — Tue, 16 Jan 2018 21:57:16 GMT

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.

Re: Prior data carried over to next row

Tom — Tue, 16 Jan 2018 22:16:40 GMT

You are going to have a problem if the variable you want to create already exists. Either drop the empty BEGIN and END variables or create your new values using different variable names. Let's make your test data into an actual dataset.

data have ;
  input date team $ group $ category $ incoming outgoing ;
  informat date yymmdd. ;
  format date yymmdd10. ;
  put (_all_)(+1);
cards;
2013-06-25  A  1  AUTO  1  0
2013-07-02  A  1  AUTO  1  0
2013-07-09  A  1  AUTO  0  1
2013-07-10  A  1  AUTO  2  0
2013-07-11  A  1  AUTO  2  0
2013-07-12  A  1  AUTO  0  2
;

Not sure what the other variables are for, so we can just ignore them fro now. If they represent some type of grouping of the data then you will need to add some BY processing statements.

So basically you want to RETAIN the new END variable so that you can store the old value into BEGIN. The SUM statement (variable + expression) will make that happen and also initialize it to zero.

data want ;
  set have ;
  begin = end ;
  end + incoming - outgoing;
run;

Re: Prior data carried over to next row

Kurt_Bremser — Wed, 17 Jan 2018 07:28:42 GMT

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

Re: Prior data carried over to next row

Kurt_Bremser — Wed, 17 Jan 2018 07:45:13 GMT

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

Re: Prior data carried over to next row

gregor1 — Wed, 17 Jan 2018 15:34:21 GMT

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.

Re: Prior data carried over to next row

Tom — Wed, 17 Jan 2018 15:38:58 GMT

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

Re: Prior data carried over to next row

gregor1 — Wed, 17 Jan 2018 15:52:12 GMT

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

Re: Prior data carried over to next row

gregor1 — Wed, 17 Jan 2018 17:23:46 GMT

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

Re: Prior data carried over to next row

Kurt_Bremser — Wed, 17 Jan 2018 18:26:07 GMT

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

Re: Prior data carried over to next row

gregor1 — Wed, 17 Jan 2018 19:10:44 GMT

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.

Re: Prior data carried over to next row

Kurt_Bremser — Wed, 17 Jan 2018 23:02:41 GMT

Use first.lob instead of first.uw_team.

Re: Prior data carried over to next row

gregor1 — Thu, 18 Jan 2018 17:51:30 GMT

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.