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

I have a data like this:

IDApplication_NoVersionActionDate
1ABCDEFX21Submit For Approval01 July 2014
2ABCDEFX21Return02 July 2014
3ABCDEFX22Submit For Approval01 July 2014
4ABCDEFX22Return02 July 2014
5ABCDEFX22Submit For Approval05 July 2014
6ABCDEFX22Approve06 July 2014

This table is a transaction trail. The system, on new version, will copy over the previous trail.

For example, on row no. 3 a new version of the same application is created. And Row1 & 2 is copied as Row3 & 4 and trail started over.

How do I delete row no 3 and 4?

Row 3 and 4 is copy of Row 1 and 2 respectively.

A friend suggested using SELECT DISTINCT but have to remove column VERSION.

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Yes, it may then be a bit more tricky than a sort/nodupkey as you need to check previous versions.  The following should do that:

data have;
  length application_no action date $50;
  infile datalines dlm=",";
  input ID Application_No $ Version Action $ Date $;
datalines;
1,ABCDEFX2,1,Submit For Approval,01July2014
2,ABCDEFX2,1,Return,02July2014
3,ABCDEFX2,2,Submit For Approval,01July2014
4,ABCDEFX2,2,Return,02July2014
5,ABCDEFX2,2,Submit For Approval,05July2014
6,ABCDEFX2,2,Approve,06July2014
run;

proc sql;
  create table WANT as
  select  BASE.* 
  from    WORK.HAVE BASE
  where   not exists(select distinct THIS.APPLICATION_NO
                     from   WORK.HAVE THIS
                     where  THIS.APPLICATION_NO=BASE.APPLICATION_NO
                      and   THIS.ACTION=BASE.ACTION
                      and   THIS.DATE=BASE.DATE
                      and   THIS.VERSION < BASE.VERSION);
quit;

View solution in original post

5 REPLIES 5
LinusH
Tourmaline | Level 20

The key I see is application_no, Action and Date.

So PROC SORT NODUPKEY would probable do the work.

Data never sleeps
hellind
Quartz | Level 8

The key is Application_No and Version.

Therefore I need to retain row 1 and 2 for version 1, and row 5 and 6 for version 2.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Yes, it may then be a bit more tricky than a sort/nodupkey as you need to check previous versions.  The following should do that:

data have;
  length application_no action date $50;
  infile datalines dlm=",";
  input ID Application_No $ Version Action $ Date $;
datalines;
1,ABCDEFX2,1,Submit For Approval,01July2014
2,ABCDEFX2,1,Return,02July2014
3,ABCDEFX2,2,Submit For Approval,01July2014
4,ABCDEFX2,2,Return,02July2014
5,ABCDEFX2,2,Submit For Approval,05July2014
6,ABCDEFX2,2,Approve,06July2014
run;

proc sql;
  create table WANT as
  select  BASE.* 
  from    WORK.HAVE BASE
  where   not exists(select distinct THIS.APPLICATION_NO
                     from   WORK.HAVE THIS
                     where  THIS.APPLICATION_NO=BASE.APPLICATION_NO
                      and   THIS.ACTION=BASE.ACTION
                      and   THIS.DATE=BASE.DATE
                      and   THIS.VERSION < BASE.VERSION);
quit;

hellind
Quartz | Level 8

Thanks, this solution worked for me.

Loko
Barite | Level 11

Hello,

Maybe this:

data have;
infile datalines truncover;
input ID Application_No $ Version Action & :$22. Date $12.;
datalines;
1 ABCDEFX2 1 Submit For Approval  01 July 2014
2 ABCDEFX2 1 Return  02 July 2014
3 ABCDEFX2 2 Submit For Approval  01 July 2014
4 ABCDEFX2 2 Return  02 July 2014
5 ABCDEFX2 2 Submit For Approval  05 July 2014
6 ABCDEFX2 2 Approve  06 July 2014
7 ABCDEFX2 3 Submit For Approval  05 July 2014
8 ABCDEFX2 3 Approve  06 July 2014
9 ABCDEFX2 3 Approve  05 July 2014
10 ABCDEFX2 3 Financed  06 July 2014
;

proc sort data=have;
by Application_No date Action  version;
run;

data want;
set have;
by Application_No date Action ;
if first.action;
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
  • 5 replies
  • 1010 views
  • 0 likes
  • 4 in conversation