Desktop productivity for business analysts and programmers

Remove values in a field and create new field

Accepted Solution Solved
Reply
Contributor
Posts: 47
Accepted Solution

Remove values in a field and create new field

I have a field called "Task". Examples of values in the field:

Text1;#Text2;#Text3;#Text_to_keep
 

 Is the a way to remove ( in the example) "Text1", "Text2" and ""Text3" and create a new field called "TaskOther" and populate this field with whatever is left over?

 

The last value in "Task" field is a free text input and can be anything. The are 17 other pre-defined selections in this field for users to select. They can select none or all of these.

 

I'll still want to keep the orginal field.

 

Hope there is something out there for this.

 

Regards

Haydn


Accepted Solutions
Solution
‎10-11-2016 12:41 AM
Super User
Posts: 5,372

Re: Remove values in a field and create new field

Here's a way to search through the text, until you find a useful piece:

 

length TextOther $ 50;

do i=1 to 30 until (TextOther > ' ');

   TextOther = scan(Task, i, '#;');

   if TextOther =: 'Text' then TextOther=' ';

end;

 

This assumes:

 

  1. There won't be more than 30 parts to the original TASK,
  2. The text that you want to save does not begin with "Text", and
  3. All the values to be ignored do begin with "Text".

 

View solution in original post


All Replies
Super User
Posts: 5,372

Re: Remove values in a field and create new field

If the field you are trying to extract is always the last one, the task is relatively simple:

 

TaskOther = scan(task, -1, '#');

 

If there is some other pattern to what you are trying to extract, you'll have to spell it out.

Contributor
Posts: 47

Re: Remove values in a field and create new field

Hi,

 

Thanks for that. Unfortunately it wont always be the last one. The field can contain nothing or the free text input wont alway's be the last value. If the users does use the free text field it will be the last value.

 

There are 17 values (excluding whatever the user inputs into the free text field) that I will now could be in the field and I was hoping to hard code these and leave whatever is left.

Super User
Posts: 5,372

Re: Remove values in a field and create new field

So what happens with an unselected field?  Does it literally contain "Text1" or "Text2" (etc.)?

Contributor
Posts: 47

Re: Remove values in a field and create new field

it would contain nothing.
Solution
‎10-11-2016 12:41 AM
Super User
Posts: 5,372

Re: Remove values in a field and create new field

Here's a way to search through the text, until you find a useful piece:

 

length TextOther $ 50;

do i=1 to 30 until (TextOther > ' ');

   TextOther = scan(Task, i, '#;');

   if TextOther =: 'Text' then TextOther=' ';

end;

 

This assumes:

 

  1. There won't be more than 30 parts to the original TASK,
  2. The text that you want to save does not begin with "Text", and
  3. All the values to be ignored do begin with "Text".

 

Contributor
Posts: 47

Re: Remove values in a field and create new field

[ Edited ]

thanks for that. My apologies the Text1, text2 etc were examples only. Some examples of the output in this field is: "Community/Stakeholder Engagement;#Emails#ACBF"


In this case I'd like ACBF to be kept and all other values removed.

 

The code still works as is, except that it returns the 1st value in the field. Probably because I explained my requirements poorly in my 1st post. Again my apologies for this.

Contributor
Posts: 47

Re: Remove values in a field and create new field

I've used your code and made some changes. This dies give me my result,admittedly, I'll have to code a few more "if's". But this is great. Thanks again

 

do i=1 to 30 until (TaskOtherII > ' ');

TaskOtherII = scan(Task, i, '#;');

if TaskOtherII =: 'Phone Calls' then TaskOtherII=' ';

if TaskOtherII =: 'Data Analysis' then TaskOtherII=' ';

if TaskOtherII =: 'Outreach' then TaskOtherII=' ';

if TaskOtherII =: 'Emails' then TaskOtherII=' ';

if TaskOtherII =: 'Outreach' then TaskOtherII=' ';

end;

Respected Advisor
Posts: 4,138

Re: Remove values in a field and create new field

[ Edited ]

If you give us some sample data which is representative of what you're really dealing with then may be we can come-up with an approach which is both simple and fully working.

 

See example code below which is based on the assumption that your wanted sub-string is always at the end of your string.


/* example for data where the wanted sub-string is always at the end of the string */
data have;
  infile datalines truncover;
  input have_str $80.;
  datalines;
Phone Calls##Outreach#Emails#Outreach#Wanted String
Phone Calls#Data Analysis#Outreach#Emails#Outreach#Wanted String
Phone Calls#Data Analysis#Outreach#Emails#Outreach#
Phone Calls#Data Analysis#Outreach#Emails#Outreach#Wanted String
##Outreach###
##Outreach###Wanted String
;
run;

data want;
  set have;
  length want_str $20;
  want_str=scan(have_str,-1,'#','m');
run;

 

Super User
Posts: 5,372

Re: Remove values in a field and create new field

Many valid programming styles exist.  Consider whether you would want to use this variation:

 

if TaskOtherII in : ('Phone Calls',

                     'Data Analysis',

                     'Outreach',

                     'Emails',

                     'Outreach')

then TaskOtherII=' ';

☑ This topic is solved.

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

Discussion stats
  • 9 replies
  • 397 views
  • 3 likes
  • 3 in conversation