DATA Step, Macro, Functions and more

how to delete row data base on variable conditions

Accepted Solution Solved
Reply
Contributor
Posts: 59
Accepted Solution

how to delete row data base on variable conditions

Hi All,

 

I have a dataset as per below:-

 

YYYYMMDDACCTNOOUTSTANDINGDUE
20170810ACCT 110.000000000000000000
20170810ACCT245.000000000000000000
20170811ACCT220.000000000000000000
20170812ACCT230.000000000000000000
20170813ACCT240.000000000000000000
20170814ACCT240.000000000000000001
20170815ACCT245.000000000000000002
20170816ACCT245.000000000000000003
20170811ACCT320.000000000000000000
20170812ACCT320.000000000000000000
20170813ACCT320.000000000000000000
20170819ACCT320.000000000000000000
20170820ACCT430.000000000000000000
20170821ACCT430.000000000000000000
20170821ACCT434.000000000000000000
20170822ACCT430.000000000000000000
20170823ACCT432.000000000000000001
20170824ACCT432.000000000000000002
20170825ACCT432.000000000000000003
20170826ACCT433.000000000000000004

 

how I'm able to get output as per below:-

YYYYMMDDACCTNOOUTSTANDINGDUE
20170810ACCT 110.000000000000000000
20170810ACCT245.000000000000000000
20170814ACCT240.000000000000000001
20170815ACCT245.000000000000000002
20170816ACCT245.000000000000000003
20170811ACCT320.000000000000000000
20170820ACCT430.000000000000000000
20170823ACCT432.000000000000000001
20170824ACCT432.000000000000000002
20170825ACCT432.000000000000000003
20170826ACCT433.000000000000000004

 

The logic would be I want the first record date(YYYYMMDD) account was recorded.

Secondly I want those record when due become 1, 2, 3 and so on.

 

Thank in advance


Accepted Solutions
Solution
‎09-13-2017 04:56 AM
Frequent Contributor
Posts: 100

Re: how to delete row data base on variable conditions

proc sort data=HAVE;
  by ACCTNO YYYYMMDD;
run;

data WANT;
  set HAVE;
  by ACCTNO;
  if first.acctno and due=0 then output;
  if due>0 then output;
run;

*Note: DUE should have the many preceeding zeroes.;
  

View solution in original post


All Replies
Super User
Posts: 19,878

Re: how to delete row data base on variable conditions

Assuming your data is sorted use BY processing.

i didn't actually check how many 0's are needed for that filter, but hopefully you get the idea.

 

data want;
set have;

by acctno yymmdd;

if first.acctno or due ne '00000000000';
run;
Solution
‎09-13-2017 04:56 AM
Frequent Contributor
Posts: 100

Re: how to delete row data base on variable conditions

proc sort data=HAVE;
  by ACCTNO YYYYMMDD;
run;

data WANT;
  set HAVE;
  by ACCTNO;
  if first.acctno and due=0 then output;
  if due>0 then output;
run;

*Note: DUE should have the many preceeding zeroes.;
  
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 95 views
  • 0 likes
  • 3 in conversation