topic Re: Can I use TRANWRD to replace multiple variable values with space in one line?: Revisited in SAS Programming

Can I use TRANWRD to replace multiple variable values with space in one line?: Revisited

Yoko — Wed, 22 Nov 2017 15:45:11 GMT

I recently posted a question and found a solution (Can I use TRANWRD to replace multiple variable values with space in one line?).

I would like to ask further questions regarding that post.

In reality, I do have a data set x (please see below) and more countries to remove than to keep.

The program below requires making a list of countries to be deleted. It will be troublesome if there are a lot of countries.

Since countries to be kept are only a few. Is it possible to make a list of countries to be kept and use it to get the same result?

#####

data countries(keep=country rename=(country=del));
input country $ 1-20;
datalines;
USA
Canada
;

data x(drop=del);
input country $1-40;
do i = 1 to n;
set countries point=i nobs=n;
country = tranwrd(country, trim(del), ' ');
end;
datalines;
China USA Japan
Korea China
Canada
;
run;

proc print; run;

Obs country
1 China Japan
2 Korea China
3

####

Thank you,

Yoko

Re: Can I use TRANWRD to replace multiple variable values with space in one line?: Revisited

art297 — Wed, 22 Nov 2017 16:23:35 GMT

You could always just reverse your current logic. e.g.:

data keeps(keep=country rename=(country=del));
  input country $ 1-20;
  datalines;
Korea
China
;

data x(drop=del);
  array keepers(99) $ _temporary_;
  retain keepers;
  if _n_ eq 1 then do i = 1 to n;
    set keeps point=i nobs=n;
    keepers(i)=del;
  end;

  input country $1-40;

  i=1;
  do while(scan(country,i,' ') ne '');
    if scan(country,i,' ') not in keepers then
      country = tranwrd(country, trim(scan(country,i,' ')), ' ');
    i+1;
  end;
  datalines;
China USA Japan
Korea China
Canada
;

Art, CEO, AnalystFinder.com

Re: Can I use TRANWRD to replace multiple variable values with space in one line?: Revisited

Yoko — Wed, 22 Nov 2017 17:45:09 GMT

Hello,

Thank you for the program. I run it and I got the same result as the one using the 'dropping' method.

But, I realized that the data set called 'keeps' has only Korea and China.

But, Japan was still kept as well.

The program is a bit more complicated for me to tell why Japan was still kept. (This is what I want, though)

Would you mind explaining this?

Thank you,

Yoko

Re: Can I use TRANWRD to replace multiple variable values with space in one line?: Revisited

art297 — Wed, 22 Nov 2017 18:31:04 GMT

It didn't work correctly because my logic was slightly flawed. It was trying to simultaneously search across a string but, at the same time, change the strings contents.

I think that the following actually does work and will be easier to follow:

data keeps(keep=country rename=(country=del));
  input country $ 1-20;
  datalines;
Korea
China
;

data x(drop=del);
  array keepers(3) $ /*_temporary_*/;
  retain keepers;
  if _n_ eq 1 then do i = 1 to n;
    set keeps point=i nobs=n;
    keepers(i)=del;
  end;

  input country $1-40;
  original=country;

  i=1;
  do while(scan(original,i,' ') ne '');
    if scan(original,i,' ') not in keepers then
      country = tranwrd(country, scan(original,i,' '), '');
    i+1;
  end;
  datalines;
China USA Japan
Korea China USA
Canada USA Japan
;

Art, CEO, AnalystFinder.com

Re: Can I use TRANWRD to replace multiple variable values with space in one line?: Revisited

Yoko — Thu, 23 Nov 2017 14:19:36 GMT

Thank you for your suggestion. It worked! I also tried slightly differently to be similar to what I actually have to do.

This worked too (please see below). But, when I tried with my real data, the variable equivalent to 'country' was empty.

Do you have any ideas regarding what I should be careful or should pay attention when applying this to a real data set?

####

DATA myData;

input ID country $40.;
DATALINES;
1 China USA Japan
2 Korea China
3 Canada
;
run;

data keeps(keep=country rename=(country=del));
input country $ 1-20;
datalines;
Korea
China
Japan
;
run ;

data newData(drop=del);
set myData;
array keepers(3) $;
retain keepers;
if _n_ eq 1 then do i = 1 to n;
set keeps point=i nobs=n;
keepers(i)=del;
end;

original=country;

i=1;
do while(scan(original,i,' ') ne '');
if scan(original,i,' ') not in keepers then
country = tranwrd(country, scan(original,i,' '), '');
i+1;
end;
run;

proc print; run;

###

Thank you!

Yoko

Re: Can I use TRANWRD to replace multiple variable values with space in one line?: Revisited

art297 — Thu, 23 Nov 2017 14:32:09 GMT

You'll have to provide some examples of where it doesn't work. Some reasons why it wouldn't work include: capitalization differences (which you can correct with the upcase function), leading or trailing spaces (which you can correct with trim, left and/or strip functions), country names that include more than two words (which you can correct by separating countries with a delimiter other than space), and variable naming differences.

Art, CEO, AnalystFinder.com

Re: Can I use TRANWRD to replace multiple variable values with space in one line?: Revisited

Yoko — Thu, 23 Nov 2017 18:07:15 GMT

My real variable has 4 alphabets (e.g., AAAA) instead of country names.

When I submit the program, there is o error message. Simply, the variable 'country' is emply like this:

Obs ID country keepers1 keepers2 keepers3 original
1 1 JJJJ CCCC UUUU JJJJ
2 2 KKKK CCCC KKKK CCCC JJJJ KKKK CCCC
3 3 KKKK CCCC JJJJ DDDD

Re: Can I use TRANWRD to replace multiple variable values with space in one line?: Revisited

Yoko — Thu, 23 Nov 2017 18:30:26 GMT

My real variable names are 4-character codes like AAAA instead of country names.

When I submit the program, I do not get any errors. Simply the country in the results is empty like this (please see below)

while country for ID1 should be CCCC JJJJ, country for ID2 should be KKKK JJJJ, and country for ID3 should be 'space'.

The codes are all capital letters and separated by one space if there are multiple codes.

In reality there are only 3 codes I want to keep just like my country name example, and there are about 50 codes that I want to replace with space. Lastly, my data is huge. Not sure if these matter..

Obs ID country keepers1 keepers2 keepers3 original
1 1 KKKK CCCC JJJJ CCCC UUUU JJJJ
2 2 KKKK CCCC JJJJ KKKK CCCC
3 3 KKKK CCCC JJJJ DDDD

Yoko

Re: Can I use TRANWRD to replace multiple variable values with space in one line?: Revisited

art297 — Thu, 23 Nov 2017 18:39:58 GMT

Post your keepers data set in the form of a data step, as well as post 10 actual records in the form of a data step.

Art, CEO, AnalystFinder.com

Re: Can I use TRANWRD to replace multiple variable values with space in one line?: Revisited

art297 — Thu, 23 Nov 2017 19:31:12 GMT

I would hope you just made a typo, as shouldn't ID2 result in: KKKK CCCC

The following appears to be working perfectly for me (p.s. same code as I posted before):

data keeps(keep=country rename=(country=del));
  input country $ 1-20;
  datalines;
KKKK
CCCC
JJJJ
;

data x(drop=del);
  array keepers(3) $ /*_temporary_*/;
  retain keepers;
  if _n_ eq 1 then do i = 1 to n;
    set keeps point=i nobs=n;
    keepers(i)=del;
  end;

  input country $1-40;
  original=country;

  i=1;
  do while(scan(original,i,' ') ne '');
    if scan(original,i,' ') not in keepers then
      country = tranwrd(country, scan(original,i,' '), '');
    i+1;
  end;
  datalines;
CCCC UUUU JJJJ
KKKK CCCC
DDDD
;

Result:

Art, CEO, AnalystFinder.com

Re: Can I use TRANWRD to replace multiple variable values with space in one line?: Revisited

Yoko — Fri, 24 Nov 2017 19:08:03 GMT

Yes, it was my typo! I got the same results too.

And my real data is like NEXJ SELK YVRC SFOA (not real codes, but something like these)..