topic Re: Looping in Enterprise Guide in SAS Programming

Looping in Enterprise Guide

Jackme — Tue, 27 Aug 2013 21:33:03 GMT

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

Re: Looping in Enterprise Guide

Reeza — Tue, 27 Aug 2013 21:37:51 GMT

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.

Re: Looping in Enterprise Guide

Jackme — Tue, 27 Aug 2013 21:47:45 GMT

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

Re: Looping in Enterprise Guide

Reeza — Tue, 27 Aug 2013 21:56:45 GMT

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.

Re: Looping in Enterprise Guide

Jackme — Thu, 29 Aug 2013 20:16:43 GMT

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

Re: Looping in Enterprise Guide

Jackme — Tue, 03 Sep 2013 18:57:20 GMT

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

Re: Looping in Enterprise Guide

Reeza — Tue, 03 Sep 2013 19:04:53 GMT

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.

Re: Looping in Enterprise Guide

Jackme — Tue, 03 Sep 2013 20:10:52 GMT

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

Thanks

Re: Looping in Enterprise Guide

Reeza — Tue, 03 Sep 2013 21:30:52 GMT

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.

Re: Looping in Enterprise Guide

Tom — Tue, 03 Sep 2013 21:33:44 GMT

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;

Re: Looping in Enterprise Guide

Jackme — Tue, 03 Sep 2013 21:39:00 GMT

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

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

Re: Looping in Enterprise Guide

Reeza — Tue, 03 Sep 2013 21:45:34 GMT

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.

Re: Looping in Enterprise Guide

Jackme — Tue, 03 Sep 2013 23:50:38 GMT

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

Re: Looping in Enterprise Guide

fbatch — Thu, 05 Sep 2013 08:27:05 GMT

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.