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

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

1 ACCEPTED SOLUTION

Accepted Solutions
ShiroAmada
Lapis Lazuli | Level 10
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

2 REPLIES 2
Reeza
Super User

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;
ShiroAmada
Lapis Lazuli | Level 10
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.;
  

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
  • 2 replies
  • 632 views
  • 0 likes
  • 3 in conversation