Hi All,
I have a dataset as per below:-
YYYYMMDD | ACCTNO | OUTSTANDING | DUE |
20170810 | ACCT 1 | 10.000000 | 000000000000 |
20170810 | ACCT2 | 45.000000 | 000000000000 |
20170811 | ACCT2 | 20.000000 | 000000000000 |
20170812 | ACCT2 | 30.000000 | 000000000000 |
20170813 | ACCT2 | 40.000000 | 000000000000 |
20170814 | ACCT2 | 40.000000 | 000000000001 |
20170815 | ACCT2 | 45.000000 | 000000000002 |
20170816 | ACCT2 | 45.000000 | 000000000003 |
20170811 | ACCT3 | 20.000000 | 000000000000 |
20170812 | ACCT3 | 20.000000 | 000000000000 |
20170813 | ACCT3 | 20.000000 | 000000000000 |
20170819 | ACCT3 | 20.000000 | 000000000000 |
20170820 | ACCT4 | 30.000000 | 000000000000 |
20170821 | ACCT4 | 30.000000 | 000000000000 |
20170821 | ACCT4 | 34.000000 | 000000000000 |
20170822 | ACCT4 | 30.000000 | 000000000000 |
20170823 | ACCT4 | 32.000000 | 000000000001 |
20170824 | ACCT4 | 32.000000 | 000000000002 |
20170825 | ACCT4 | 32.000000 | 000000000003 |
20170826 | ACCT4 | 33.000000 | 000000000004 |
how I'm able to get output as per below:-
YYYYMMDD | ACCTNO | OUTSTANDING | DUE |
20170810 | ACCT 1 | 10.000000 | 000000000000 |
20170810 | ACCT2 | 45.000000 | 000000000000 |
20170814 | ACCT2 | 40.000000 | 000000000001 |
20170815 | ACCT2 | 45.000000 | 000000000002 |
20170816 | ACCT2 | 45.000000 | 000000000003 |
20170811 | ACCT3 | 20.000000 | 000000000000 |
20170820 | ACCT4 | 30.000000 | 000000000000 |
20170823 | ACCT4 | 32.000000 | 000000000001 |
20170824 | ACCT4 | 32.000000 | 000000000002 |
20170825 | ACCT4 | 32.000000 | 000000000003 |
20170826 | ACCT4 | 33.000000 | 000000000004 |
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
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.;
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;
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.;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.