BookmarkSubscribeRSS Feed
Jackme
Calcite | Level 5

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

13 REPLIES 13
Reeza
Super User

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
Calcite | Level 5

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
Super User

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
Calcite | Level 5
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

Jackme
Calcite | Level 5

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

Reeza
Super User

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
Calcite | Level 5

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

Thanks

Reeza
Super User

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
Calcite | Level 5

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

Reeza
Super User

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
Calcite | Level 5

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
Calcite | Level 5

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
Super User Tom
Super User

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;

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 13 replies
  • 1257 views
  • 0 likes
  • 4 in conversation