DATA Step, Macro, Functions and more

HOW TO CREATE NEW VARIABLE TAG BASE ON CURRENT VARIABLE CONDITION

Accepted Solution Solved
Reply
Contributor
Posts: 59
Accepted Solution

HOW TO CREATE NEW VARIABLE TAG BASE ON CURRENT VARIABLE CONDITION

[ Edited ]

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:-

 

 

CDCYCCODEACCTNOOUTSTANDINGDUESTATE
20170801ACCT 1100000000000009C41
20170802ACCT 1100000000000010C41
20170803ACCT 1100000000000011C41
20170804ACCT 1100000000000012C41
20170805ACCT 1155000000000013C41
20170806ACCT 1155000000000014C41
20170807ACCT 1155000000000015C41
20170808ACCT 1155000000000016C41
20170809ACCT 1160000000000017C41
20170810ACCT 1160000000000018C51
20170811ACCT 1160000000000019C51
20170812ACCT 1160000000000020C51
20170813ACCT 1160000000000021C51
20170813ACCT265000000000005V51
20170814ACCT265000000000006V05
20170815ACCT265000000000007V05
20170801ACCT3300000000000001E05
20170818ACCT4500000000000005C41
20170819ACCT4500000000000006E59
20170820ACCT4500000000000007E59
20170821ACCT4500000000000008E59
20170822ACCT4500000000000009E05
20170823ACCT4505000000000010E05
20170824ACCT4505000000000011E05
20170825ACCT4505000000000012E05
20170826ACCT4505000000000013E05
20170827ACCT4506000000000014E32
20170828ACCT4506000000000015E32
20170829ACCT4506000000000016E32
20170830ACCT4506000000000017E32
20170831ACCT4506000000000018E32


expecting outcome:-

 

CDCYCCODEACCTNOOUTSTANDINGDUESTATETAG
20170801ACCT 1100000000000009C41C41
20170802ACCT 1100000000000010C41C41
20170803ACCT 1100000000000011C41C41
20170804ACCT 1100000000000012C41C41
20170805ACCT 1155000000000013C41C41
20170806ACCT 1155000000000014C41C41
20170807ACCT 1155000000000015C41C41
20170808ACCT 1155000000000016C41C41
20170809ACCT 1160000000000017C41C41
20170810ACCT 1160000000000018C51C41
20170811ACCT 1160000000000019C51C41
20170812ACCT 1160000000000020C51C41
20170813ACCT 1160000000000021C51C41
20170813ACCT265000000000005V51V51
20170814ACCT265000000000006V05V51
20170815ACCT265000000000007V05V51
20170801ACCT3300000000000001E05E05
20170818ACCT4500000000000005C41C41
20170819ACCT4500000000000006E59C41
20170820ACCT4500000000000007E59C41
20170821ACCT4500000000000008E59C41
20170822ACCT4500000000000009E05C41
20170823ACCT4505000000000010E05C41
20170824ACCT4505000000000011E05C41
20170825ACCT4505000000000012E05C41
20170826ACCT4505000000000013E05C41
20170827ACCT4506000000000014E32C41
20170828ACCT4506000000000015E32C41
20170829ACCT4506000000000016E32C41
20170830ACCT4506000000000017E32C41
20170831ACCT4506000000000018E32C41

 

Logic is tag always follow the first ACCTNO -> state from the first record.

 

Thank in advance.


Accepted Solutions
Solution
‎09-13-2017 05:45 AM
Super User
Super User
Posts: 7,988

Re: HOW TO CREATE NEW VARIABLE TAG BASE ON CURRENT VARIABLE CONDITION

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;

View solution in original post


All Replies
Solution
‎09-13-2017 05:45 AM
Super User
Super User
Posts: 7,988

Re: HOW TO CREATE NEW VARIABLE TAG BASE ON CURRENT VARIABLE CONDITION

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;
Contributor
Posts: 59

Re: HOW TO CREATE NEW VARIABLE TAG BASE ON CURRENT VARIABLE CONDITION

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

Respected Advisor
Posts: 4,173

Re: HOW TO CREATE NEW VARIABLE TAG BASE ON CURRENT VARIABLE CONDITION

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;
☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 97 views
  • 0 likes
  • 3 in conversation