Looping in Enterprise Guide

[Jackme]

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

[Reeza]

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.

[Jackme]

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

[Reeza]

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.

[Jackme]

ID	ID1	Satus	ID2	Date1	Date2	Date3
1234	15115	EX	1444	4/10/10 11:06 AM	2/5/13 1:51 PM	4/11/2010	I want this row for this ID , ID1, ID2
1234	15115	DE	1444	4/10/10 11:06 AM	2/5/13 1:30 PM	4/11/2010
1234	15115	DE	1444	4/10/10 11:06 AM	4/10/10 11:06 AM	4/11/2010
2345	16330	EX	1436	4/10/10 10:09 AM	2/5/13 2:15 PM	4/11/2010	I want this row for this ID , ID1, ID2
2345	16330	DE	1436	4/10/10 10:09 AM	2/5/13 2:04 PM	4/11/2010
2345	16330	DE	1436	4/10/10 10:09 AM	4/10/10 10:09 AM	4/11/2010
5678	26856	DE	5482	6/24/10 9:53 AM	2/27/13 12:36 PM	8/1/2010	I want to ingore this for because it only DE status there is no other status later
5678	26856	DE	5482	6/24/10 9:53 AM	6/24/10 9:53 AM	8/1/2010
7890	32684	EX	9373	1/28/13 1:18 PM	2/8/13 4:06 PM	9/13/2010	I want this row for this ID , ID1, ID2
7890	32684	PC	9373	1/28/13 1:18 PM	2/6/13 8:33 AM	9/13/2010
7890	32684	DE	9373	1/28/13 1:18 PM	2/5/13 1:15 PM	9/13/2010
7890	32684	DE	9373	1/28/13 1:18 PM	1/28/13 1:18 PM	9/13/2010
1003	35196	PC	0756	9/17/10 2:12 PM	2/4/13 12:30 PM	10/10/2010	I want to ingore this for because it only PC as a top of stack based on Date2
1003	35196	DE	0756	9/17/10 2:12 PM	9/17/10 2:15 PM	10/10/2010	I want to ingore this for because it only PC as a top of stack based on Date3
1003	35196	DE	0756	9/17/10 2:12 PM	9/17/10 2:12 PM	10/10/2010	I want to ingore this for because it only PC as a top of stack based on Date4
7538	35230	PC	8188	8/9/10 12:03 PM	2/4/13 10:30 AM	8/1/2010	I want to ingore this for because it only PC as a top of stack based on Date5
7538	35230	DE	8188	8/9/10 12:03 PM	8/9/10 12:03 PM	8/1/2010	I want to ingore this for because it only PC as a top of stack based on Date6
5135	44478	EX	1106	3/3/11 12:18 PM	2/11/13 3:45 PM	6/20/2011	I want this row for this ID , ID1, ID2
5135	44478	DE	1106	3/3/11 12:18 PM	6/20/11 5:02 AM	6/20/2011
5135	44478	PM	1106	3/3/11 12:18 PM	3/3/11 12:18 PM	6/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

[Jackme]

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

[Reeza]

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.

[Jackme]

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

Thanks

[Reeza]

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.

[Jackme]

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

1234

15115

EX

1444

4/10/10 11:06 AM

2/5/13 1:51 PM

4/11/2010

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

ID	ID1	Satus	ID2	Date1	Date2	Date3
1234	15115	EX	1444	4/10/10 11:06 AM	2/5/13 1:51 PM	4/11/2010	I want this row for this ID , ID1, ID2
1234	15115	DE	1444	4/10/10 11:06 AM	2/5/13 1:30 PM	4/11/2010
1234	15115	DE	1444	4/10/10 11:06 AM	4/10/10 11:06 AM	4/11/2010
2345	16330	EX	1436	4/10/10 10:09 AM	2/5/13 2:15 PM	4/11/2010	I want this row for this ID , ID1, ID2
2345	16330	DE	1436	4/10/10 10:09 AM	2/5/13 2:04 PM	4/11/2010
2345	16330	DE	1436	4/10/10 10:09 AM	4/10/10 10:09 AM	4/11/2010

Thanks

[Reeza]

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.

[Jackme]

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

[fbatch]

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.

[Tom]

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;