Dear All,
Any idea of how to tag/remark a certain group of acc base on example acc no. my dataset will be as below:-
CDCYCCODE | ACCTNO | OUTSTANDING | DUE | STATE |
20170801 | ACCT 1 | 100 | 000000000009 | C41 |
20170802 | ACCT 1 | 100 | 000000000010 | C41 |
20170803 | ACCT 1 | 100 | 000000000011 | C41 |
20170804 | ACCT 1 | 100 | 000000000012 | C41 |
20170805 | ACCT 1 | 155 | 000000000013 | C41 |
20170806 | ACCT 1 | 155 | 000000000014 | C41 |
20170807 | ACCT 1 | 155 | 000000000015 | C41 |
20170808 | ACCT 1 | 155 | 000000000016 | C41 |
20170809 | ACCT 1 | 160 | 000000000017 | C41 |
20170810 | ACCT 1 | 160 | 000000000018 | C51 |
20170811 | ACCT 1 | 160 | 000000000019 | C51 |
20170812 | ACCT 1 | 160 | 000000000020 | C51 |
20170813 | ACCT 1 | 160 | 000000000021 | C51 |
20170813 | ACCT2 | 65 | 000000000005 | V51 |
20170814 | ACCT2 | 65 | 000000000006 | V05 |
20170815 | ACCT2 | 65 | 000000000007 | V05 |
20170801 | ACCT3 | 300 | 000000000001 | E05 |
20170818 | ACCT4 | 500 | 000000000005 | C41 |
20170819 | ACCT4 | 500 | 000000000006 | E59 |
20170820 | ACCT4 | 500 | 000000000007 | E59 |
20170821 | ACCT4 | 500 | 000000000008 | E59 |
20170822 | ACCT4 | 500 | 000000000009 | E05 |
20170823 | ACCT4 | 505 | 000000000010 | E05 |
20170824 | ACCT4 | 505 | 000000000011 | E05 |
20170825 | ACCT4 | 505 | 000000000012 | E05 |
20170826 | ACCT4 | 505 | 000000000013 | E05 |
20170827 | ACCT4 | 506 | 000000000014 | E32 |
20170828 | ACCT4 | 506 | 000000000015 | E32 |
20170829 | ACCT4 | 506 | 000000000016 | E32 |
20170830 | ACCT4 | 506 | 000000000017 | E32 |
20170831 | ACCT4 | 506 | 000000000018 | E32 |
expecting outcome:-
CDCYCCODE | ACCTNO | OUTSTANDING | DUE | STATE | TAG |
20170801 | ACCT 1 | 100 | 000000000009 | C41 | C41 |
20170802 | ACCT 1 | 100 | 000000000010 | C41 | C41 |
20170803 | ACCT 1 | 100 | 000000000011 | C41 | C41 |
20170804 | ACCT 1 | 100 | 000000000012 | C41 | C41 |
20170805 | ACCT 1 | 155 | 000000000013 | C41 | C41 |
20170806 | ACCT 1 | 155 | 000000000014 | C41 | C41 |
20170807 | ACCT 1 | 155 | 000000000015 | C41 | C41 |
20170808 | ACCT 1 | 155 | 000000000016 | C41 | C41 |
20170809 | ACCT 1 | 160 | 000000000017 | C41 | C41 |
20170810 | ACCT 1 | 160 | 000000000018 | C51 | C41 |
20170811 | ACCT 1 | 160 | 000000000019 | C51 | C41 |
20170812 | ACCT 1 | 160 | 000000000020 | C51 | C41 |
20170813 | ACCT 1 | 160 | 000000000021 | C51 | C41 |
20170813 | ACCT2 | 65 | 000000000005 | V51 | V51 |
20170814 | ACCT2 | 65 | 000000000006 | V05 | V51 |
20170815 | ACCT2 | 65 | 000000000007 | V05 | V51 |
20170801 | ACCT3 | 300 | 000000000001 | E05 | E05 |
20170818 | ACCT4 | 500 | 000000000005 | C41 | C41 |
20170819 | ACCT4 | 500 | 000000000006 | E59 | C41 |
20170820 | ACCT4 | 500 | 000000000007 | E59 | C41 |
20170821 | ACCT4 | 500 | 000000000008 | E59 | C41 |
20170822 | ACCT4 | 500 | 000000000009 | E05 | C41 |
20170823 | ACCT4 | 505 | 000000000010 | E05 | C41 |
20170824 | ACCT4 | 505 | 000000000011 | E05 | C41 |
20170825 | ACCT4 | 505 | 000000000012 | E05 | C41 |
20170826 | ACCT4 | 505 | 000000000013 | E05 | C41 |
20170827 | ACCT4 | 506 | 000000000014 | E32 | C41 |
20170828 | ACCT4 | 506 | 000000000015 | E32 | C41 |
20170829 | ACCT4 | 506 | 000000000016 | E32 | C41 |
20170830 | ACCT4 | 506 | 000000000017 | E32 | C41 |
20170831 | ACCT4 | 506 | 000000000018 | E32 | C41 |
Logic is tag always follow the first ACCTNO -> state from the first record.
Thank in advance.
Please do not type all in uppercase!
Also, for future post test data in the form of a datastep (only need a few rows), as such I am not typing that in so this is just an untested bit of code:
data want; set have; by acctno; retain tag; if first.acctno then tag=state; run;
Please do not type all in uppercase!
Also, for future post test data in the form of a datastep (only need a few rows), as such I am not typing that in so this is just an untested bit of code:
data want; set have; by acctno; retain tag; if first.acctno then tag=state; run;
Hi RW9,
Thank for the code and guidance, it work.
Apologise for inconvenience caused. Noted the test data and wording(uppercase), next time will use lowercase.
Best Regards
Same as @RW9 with only a small change to the by group to ensure that data is sorted as expected.
data want;
set have;
by acctno CDCYCCODE;
retain tag;
if first.acctno then tag=state;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.