HOW TO CREATE NEW VARIABLE TAG BASE ON CURRENT VARIABLE CONDITION

[sagulolo]

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.

[RW9 · Accepted solution]

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;

[sagulolo]

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

[Patrick]

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;