Looping in Enterprise Guide

Reply
Occasional Contributor
Posts: 12

Looping in Enterprise Guide

Hello All..

I'm new to SAS .. We have a requirement to compare each row in a table based on a key column and select a based on business rules. I want to know if we have a way to loop through all the rows in a table using Enterprise Guide tool.

Pls advice or point me in a right direction

Thanks

Super User
Posts: 17,775

Re: Looping in Enterprise Guide

I don't think you need a loop.

Provide some more details. It sounds more like a merge and some criteria, which can probably be done in the filter and query tool.

Occasional Contributor
Posts: 12

Re: Looping in Enterprise Guide

Thanks Reeza ..Here is the data for me

C1      C2     C3     C4     C5                    C6

---------------------------------------------------------------------

00      DE    test    me    jan-01-02      jan-01-02

00      DE    test2   me    jan-01-03     jan-01-02

00      DE    test3    me   jan-01-04     jan-01-02

00      PC    test3    me   jan-01-04     jan-01-02

00      FR    test3    me   jan-01-05     jan-01-02

With this data i want to get only last row which is  - 00      FR    test3    me   jan-01-05     jan-01-02 ..it's not guarentee that it will FR in C2 ..there could be any value..business rule is to take the max DE row if exists from there exclude any PC rows and go to next row to find the date in column C5..pls advice on this...

Super User
Posts: 17,775

Re: Looping in Enterprise Guide

Generally a good way to ask the question is to put a table that shows what your data looks like and what the output is.

I'd recommend posting some more examples that meet all your requirements and include what you want the final output to look like as well.

This is not a loop problem in SAS terminology.

Occasional Contributor
Posts: 12

Re: Looping in Enterprise Guide

IDID1SatusID2Date1Date2Date3
123415115EX14444/10/10 11:06 AM2/5/13 1:51 PM4/11/2010I want this row for this ID , ID1, ID2
123415115DE14444/10/10 11:06 AM2/5/13 1:30 PM4/11/2010
123415115DE14444/10/10 11:06 AM4/10/10 11:06 AM4/11/2010
234516330EX14364/10/10 10:09 AM2/5/13 2:15 PM4/11/2010I want this row for this ID , ID1, ID2
234516330DE14364/10/10 10:09 AM2/5/13 2:04 PM4/11/2010
234516330DE14364/10/10 10:09 AM4/10/10 10:09 AM4/11/2010
567826856DE54826/24/10 9:53 AM2/27/13 12:36 PM8/1/2010I want to ingore this for because it only DE status there is no other status later
567826856DE54826/24/10 9:53 AM6/24/10 9:53 AM8/1/2010
789032684EX93731/28/13 1:18 PM2/8/13 4:06 PM9/13/2010I want this row for this ID , ID1, ID2
789032684PC93731/28/13 1:18 PM2/6/13 8:33 AM9/13/2010
789032684DE93731/28/13 1:18 PM2/5/13 1:15 PM9/13/2010
789032684DE93731/28/13 1:18 PM1/28/13 1:18 PM9/13/2010
100335196PC07569/17/10 2:12 PM2/4/13 12:30 PM10/10/2010I want to ingore this for because it only PC as a top of stack based on Date2
100335196DE07569/17/10 2:12 PM9/17/10 2:15 PM10/10/2010I want to ingore this for because it only PC as a top of stack based on Date3
100335196DE07569/17/10 2:12 PM9/17/10 2:12 PM10/10/2010I want to ingore this for because it only PC as a top of stack based on Date4
753835230PC81888/9/10 12:03 PM2/4/13 10:30 AM8/1/2010I want to ingore this for because it only PC as a top of stack based on Date5
753835230DE81888/9/10 12:03 PM8/9/10 12:03 PM8/1/2010I want to ingore this for because it only PC as a top of stack based on Date6
513544478EX11063/3/11 12:18 PM2/11/13 3:45 PM6/20/2011I want this row for this ID , ID1, ID2
513544478DE11063/3/11 12:18 PM6/20/11 5:02 AM6/20/2011
513544478PM11063/3/11 12:18 PM3/3/11 12:18 PM6/20/2011

Here is the sample data ..pls check do let me know if we can compare each row based on key columns in EGuide.

Thanks

Occasional Contributor
Posts: 12

Re: Looping in Enterprise Guide

Team - Any ideas how we can do this using EGuide or with some programming in EGuide

Super User
Posts: 17,775

Re: Looping in Enterprise Guide

I don't think you've explained your logic clearly enough...at least for me it would take a while to try and guess at it.

Occasional Contributor
Posts: 12

Re: Looping in Enterprise Guide

Thanks for checking. Pls do let me know if you need more details or info ..

Thanks

Super User
Posts: 17,775

Re: Looping in Enterprise Guide

More details.  See here:

For example what about records where you have no indicator beside it?

Also, What uniquely identifies a group, is it ID, ID2, ID3 or are the dates important? If the dates are important are the times? 

Please take the time to detail your question if you expect a detailed response.

Occasional Contributor
Posts: 12

Re: Looping in Enterprise Guide

Thanks for your time and help tips.

Yes ID,ID1,ID2 are key columns.With below sample data I want to compare data for ID,ID1,ID2 in each row ..Example for ID 1234 we have Satus DE,DE and EX rows..for this combination take max DE row and after that row find next EX row ..in this case this is the row which I want in my output file

123415115EX14444/10/10 11:06 AM2/5/13 1:51 PM4/11/2010I want this row for this ID , ID1, ID2

I hope this is clear to some extent to give some idea on how we can do it..

Sample Data...

IDID1SatusID2Date1Date2Date3
123415115EX14444/10/10 11:06 AM2/5/13 1:51 PM4/11/2010I want this row for this ID , ID1, ID2
123415115DE14444/10/10 11:06 AM2/5/13 1:30 PM4/11/2010
123415115DE14444/10/10 11:06 AM4/10/10 11:06 AM4/11/2010
234516330EX14364/10/10 10:09 AM2/5/13 2:15 PM4/11/2010I want this row for this ID , ID1, ID2
234516330DE14364/10/10 10:09 AM2/5/13 2:04 PM4/11/2010
234516330DE14364/10/10 10:09 AM4/10/10 10:09 AM4/11/2010

Thanks

Super User
Posts: 17,775

Re: Looping in Enterprise Guide

Lets assume that you can sort it then. Working in EG:

Use a query step to :

1. calculate min of status

2. Calculate max of status

3. In having Clause check that min(status) ne max(status) . This makes sure your records have changed status and the only one isn't PC.  If you can have all DE and that be okay, change the logic to max(status)=min(status) and max(status) ne "PC" 

4. You can then use a data step as Tom has suggested to obtain the first (first. logic) or you can add some logic like the max(date1)=date1 to the query to have it select only one row.

Good luck.

Occasional Contributor
Posts: 12

Re: Looping in Enterprise Guide

Thanks Reeza and Tom . I'm trying to implement the solutions in EG . There is some success . I will update the team once I reach my target data set.

Thanks

Occasional Contributor
Posts: 7

Re: Looping in Enterprise Guide

Hi,

this helps you get what you want...

Assume that toto is your initial dataset

proc sort data=toto out=titi;

by id id1 id2 date1 date2 status;

run;

data tata;

set titi;

by id id1 id2 date1 date2 status;

if last.date1;

if status in ('PC', 'DE') then delete;

run;

BR.

Super User
Super User
Posts: 6,498

Re: Looping in Enterprise Guide

I sounds like you want to group your data by ID, ID1, ID2, DATE1.

Then you want to sort by DATE2.

If the last (latest) record has status = 'PC' the remove all for that group.

Otherwise keep only the last (latest) record.

These two requirements reduce to keep only the latest record and deleting it if the status is 'PC'.

proc sort data=HAVE  ;

  by id id1 id2 date1 date2 ;

run;

data want ;

set HAVE ;

by id id1 id2 date1 date2 ;

if last.date1 ;

if status='PC' then delete ;

run;

Ask a Question
Discussion stats
  • 13 replies
  • 396 views
  • 0 likes
  • 4 in conversation