BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
gregor1
Quartz | Level 8

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,

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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 

View solution in original post

17 REPLIES 17
Kurt_Bremser
Super User
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.

gregor1
Quartz | Level 8

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.

ballardw
Super User

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.

gregor1
Quartz | Level 8

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. 

Kurt_Bremser
Super User

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

Kurt_Bremser
Super User

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 
gregor1
Quartz | Level 8

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.

Tom
Super User Tom
Super User

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

gregor1
Quartz | Level 8

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

gregor1
Quartz | Level 8

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

gregor1
Quartz | Level 8

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.

gregor1
Quartz | Level 8

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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