topic Re: How to Concatenate multiple Colns to one var/col excluding unnecessary words in New SAS User

How to Concatenate multiple Colns to one var/col excluding unnecessary words

maxjiang6999 — Mon, 29 Jul 2019 00:26:11 GMT

First I split the original company names by space and get var1-var16

data that have:

company names var1 var2 var3 .... var16

A & B A & B

Pear Inc. Pear Inc.

Pears Corp. Pears Corp.

the ideal output:

company names var1 var2 var3 .... var16 reduced_name

A & F A & F A F

Pear Inc. Pear Inc. Pear

Pears Corp. Pears Corp. Pears

The format of the company names is Char 30. (so there're different trailing blanks). So when I directly cat() or cats() them, I don't get what I want.

Can anyone help?

Re: How to Concatenate multiple Colns to one var/col excluding unnecessary words

Astounding — Mon, 29 Jul 2019 02:00:39 GMT

You don't show any of the rules for calculating the REDUCED_NAME, so I will leave that part to you.

The function you should be using is CATX, to leave a single blank between the pieces of the reduced name. For example:

reduced_name = catx(' ', var1, var3);

Re: How to Concatenate multiple Colns to one var/col excluding unnecessary words

Reeza — Mon, 29 Jul 2019 02:18:41 GMT

What about using TRANWRD() instead to replace the words you don't want?

If all the terms are in a separate file you can load the temporary array from the file.

data clean;
set have;

array temp(7) $ _temporary_ ('INC', 'INC.', '&', 'CO', 'CO.', 'LTD.', 'LTD');

cleanVar = oldVar;

do i=1 to dim(temp);
cleanVar = tranwrd(cleanVar, temp(i), "");
end;

run;

Re: How to Concatenate multiple Colns to one var/col excluding unnecessary words

maxjiang6999 — Mon, 29 Jul 2019 02:34:34 GMT

Thanks for the quick reply. What I want to do for reduced_name is to exclude words like: "LTD","HOLDINGS","CORP","CORP.","CORPORATION","&","LLC","THE". I want to remove those "unimportant" words. Can you help on this?

Re: How to Concatenate multiple Colns to one var/col excluding unnecessary words

maxjiang6999 — Mon, 29 Jul 2019 02:36:52 GMT

I tried this code but it didn't work out.

The log only said: numeric values have been converted to character values at he places give by XXX. Variable oldvar is uninitialized.

There is no error. But in my result, I only see missing values.

Re: How to Concatenate multiple Colns to one var/col excluding unnecessary words

Reeza — Mon, 29 Jul 2019 02:53:13 GMT

@maxjiang6999 wrote:

I tried this code but it didn't work out.

The log only said: numeric values have been converted to character values at he places give by XXX. Variable oldvar is uninitialized.

There is no error. But in my result, I only see missing values.

Did you change the variable names to reflect your variable names? 'didn't work out' isn't very informative, it doesn't convey any information about what you did, what happened and what the errors were. Including the code you ran and log is much more helpful.

I tested it and it works partly for me, this was intended to get you started, by showing the process. You'll need to determine the rules and details because cleaning data is a finicky task and tedious. The part not working is the & and I did change it to make everything uppercase because INC does not match inc. You should get the new variable as an uppercase variable regardless.

 78         data clean;
 79         set have;
 80         
 81         array temp(8) $ _temporary_ ('INC', 'INC.', '&', 'CO', 'CO.', 'LTD.', 'LTD', 'CORP');
 82         
 83         cleanVar = upcase(oldVar);
 84         
 85         do i=1 to dim(temp);
 86         cleanVar = tranwrd(cleanVar, temp(i), "");
 87         end;
 88         
 89         run;
 
 NOTE: There were 3 observations read from the data set WORK.HAVE.
 NOTE: The data set WORK.CLEAN has 3 observations and 3 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds

Full code:

data have;
input oldVar $20.;
cards;
A & B
Pears Inc.
Pears Corp
;;;;
run;

data clean;
set have;

array temp(8) $ _temporary_ ('INC', 'INC.', '&', 'CO', 'CO.', 'LTD.', 'LTD', 'CORP');

cleanVar = upcase(oldVar);

do i=1 to dim(temp);
    cleanVar = tranwrd(cleanVar, temp(i), "");
end;

run;

@maxjiang6999 wrote:

I tried this code but it didn't work out.

The log only said: numeric values have been converted to character values at he places give by XXX. Variable oldvar is uninitialized.

There is no error. But in my result, I only see missing values.

Re: How to Concatenate multiple Colns to one var/col excluding unnecessary words

PGStats — Mon, 29 Jul 2019 04:20:30 GMT

You could use regular expressions:

data have;
length name $32;
input name $32.;
datalines;
A & B
Pear Inc.
Pears Corp. 
;

data want;
length cleanName $32;
if not prxID then 
    prxID + prxparse("s/\b(INC|LTD|HOLDINGS|CORP|CORPORATION|LLC|THE)\b\.?//io");
set have;
cleanName = compbl(prxChange(prxID, -1, translate(name," ", "&")));
drop prxID;
run;

proc print data=want noobs; run;

                                clean
                                Name     name

                                A B      A & B
                                Pear     Pear Inc.
                                Pears    Pears Corp.

Re: How to Concatenate multiple Colns to one var/col excluding unnecessary words

maxjiang6999 — Mon, 29 Jul 2019 17:12:02 GMT

Sorry that I was not very clear about my question at the first place. Let me explain it in another way.

What I have: the original company name

What I want: a new column (reduced_name) = company name exclude ('INC.','COMPANY','INC','GROUP','CORPORATION','&','LLC','LTD','THE', 'LTD.', 'HOLDINGS', 'CORP', 'PLC', 'CORP.')

What I did: 1) separate the company name using SCAN() by space and get var1-var16 for each element/word of the name 2) concatenate var1-var16 using CATX(' ', OF var1-var16) based on so many IF conditions (what you see is just part). (please see the screenshot below)

Is there any way that's more convenient to conclude my IF statements?

Thanks for all the help from you guys.

Re: How to Concatenate multiple Colns to one var/col excluding unnecessary words

maxjiang6999 — Mon, 29 Jul 2019 17:12:49 GMT

What I get is still all missing values.

Re: How to Concatenate multiple Colns to one var/col excluding unnecessary words

PGStats — Mon, 29 Jul 2019 17:17:46 GMT

Can't guess what's wrong without seeing what you tried. My example up there DOES work.

Re: How to Concatenate multiple Colns to one var/col excluding unnecessary words

maxjiang6999 — Mon, 29 Jul 2019 17:30:44 GMT

Hi I re-tried the code and it did work out partially. What I did:

data test; set peers4;
array temp(8) $_temporary_ ('INC.','COMPANY','INC','GROUP','CORPORATION','&','LLC','LTD','THE', 'LTD.', 'HOLDINGS', 'CORP', 'PLC', 'CORP.','CO');
cleanvar = upcase(peername);
do i = 1 to dim(temp);
cleanvar = tranwrd(cleanvar, temp(i),'');
end;
run;

Then the log said: too many values for initialization of the array temp. Excess values are ignored.

Can you help? Really appreciate it!

Re: How to Concatenate multiple Colns to one var/col excluding unnecessary words

maxjiang6999 — Mon, 29 Jul 2019 18:03:42 GMT

I re-tried the code and it works! Thank you very much. But I do have a following question. In my dataset, I have some companies named as INC XXX Inc.. If I applied the code to those, I will only get XXX. Is there any way for me to just get rid of the Inc. at the end?

Thanks again!

Re: How to Concatenate multiple Colns to one var/col excluding unnecessary words

PGStats — Mon, 29 Jul 2019 19:04:53 GMT

There is always a way. But the code I submitted was based on your stated requirement, it doesn't do that.The function prxchange finds matching patterns in the target string from left to right, so I think that you can't get it to change only the last match.

Re: How to Concatenate multiple Colns to one var/col excluding unnecessary words

Reeza — Mon, 29 Jul 2019 19:40:09 GMT

array temp(8)

The 8 here indicates the number of elements in the temporary array. You need to increase it to account for the new words you've added.

I don't know if that can be dynamic.