topic Re: Remove values in a field and create new field in SAS Enterprise Guide

Remove values in a field and create new field

Haydn — Tue, 11 Oct 2016 02:16:55 GMT

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

Re: Remove values in a field and create new field

Astounding — Tue, 11 Oct 2016 02:35:10 GMT

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.

Re: Remove values in a field and create new field

Haydn — Tue, 11 Oct 2016 02:41:18 GMT

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.

Re: Remove values in a field and create new field

Astounding — Tue, 11 Oct 2016 02:50:08 GMT

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

Re: Remove values in a field and create new field

Haydn — Tue, 11 Oct 2016 02:59:14 GMT

it would contain nothing.

Re: Remove values in a field and create new field

Astounding — Tue, 11 Oct 2016 03:44:22 GMT

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:

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

Re: Remove values in a field and create new field

Haydn — Tue, 11 Oct 2016 03:54:22 GMT

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.

Re: Remove values in a field and create new field

Haydn — Tue, 11 Oct 2016 04:14:59 GMT

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;

Re: Remove values in a field and create new field

Patrick — Tue, 11 Oct 2016 04:44:16 GMT

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;

Re: Remove values in a field and create new field

Astounding — Tue, 11 Oct 2016 17:28:07 GMT

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=' ';