DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 26
Accepted Solution

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

Hello, 

 

I have a variable, say, country.  

I wonder if it's possible to search what are values in the variable (e.g., Canada, US, Japan, Korea, China), then replace Canada and US with space. 

 

I know I can do it one by one:

country = TRANWRD (country,'Canada','');
country = TRANWRD (country,'US,'');. 

 

But, when there are a lot of values (e.g., 50), I wish I do not have to write 50 lines line this. 

 

I appreciate any suggestions. 

 

Thank you, 

 

Yoko

 


Accepted Solutions
Solution
‎11-22-2017 10:25 AM
SAS Super FREQ
Posts: 508

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

One of our sample data sets, the vital statistics data set, has a bunch of country names, so I modified it to make a data set countries, with a variable Del that has country names.  Then I show how to process it repeatedly to remove country names from a couple of strings in another data set.  You could do something like this.  You could use arrays of temporary variables as well.  I am sure there are other ways, but these are the two that come to mind.

data countries(keep=country rename=(country=del));
   input country $ 1-20 Births Deaths;
   datalines;
USA                  15  9
Afghanistan          52 30
Algeria              50 16
Angola               47 23
Argentina            22 10
Australia            16  8
Austria              12 13
Bangladesh           47 19
Belgium              12 12
Brazil               36 10
Bulgaria             17 10
Burma                38 15
Cameroon             42 22
Canada               16  7
Chile                22  7
China                31 11
Taiwan               26  5
Colombia             34 10
Cuba                 20  6
Czechoslovakia       19 11
Ecuador              42 11
Egypt                39 13
Ethiopia             48 23
France               14 11
German Dem Rep       12 14
Germany, Fed Rep of  10 12
Ghana                46 14
Greece               16  9
Guatemala            40 14
Hungary              18 12
India                36 15
Indonesia            38 16
Iran                 42 12
Iraq                 48 14
Italy                14 10
Ivory Coast          48 23
Japan                16  6
Kenya                50 14
Korea, Dem Peo Rep   43 12
Korea, Rep of        26  6
Madagascar           47 22
Malaysia             30  6
Mexico               40  7
Morocco              47 16
Mozambique           45 18
Nepal                46 20
Netherlands          13  8
Nigeria              49 22
Pakistan             44 14
Peru                 40 13
Philippines          34 10
Poland               20  9
Portugal             19 10
Rhodesia             48 14
Romania              19 10
Saudi Arabia         49 19
South Africa         36 12
Spain                18  8
Sri Lanka            26  9
Sudan                49 17
Sweden               12 11
Switzerland          12  9
Syria                47 14
Tanzania             47 17
Thailand             34 10
Turkey               34 12
USSR                 18  9
Uganda               48 17
United Kingdom       12 12
Upper Volta          50 28
Venezuela            36  6
Vietnam              42 17
Yugoslavia           18  8
Zaire                45 18
;


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;
This is a USA line.
Here we have Yugoslavia and Zaire.
;   

proc print; run;

View solution in original post


All Replies
Solution
‎11-22-2017 10:25 AM
SAS Super FREQ
Posts: 508

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

One of our sample data sets, the vital statistics data set, has a bunch of country names, so I modified it to make a data set countries, with a variable Del that has country names.  Then I show how to process it repeatedly to remove country names from a couple of strings in another data set.  You could do something like this.  You could use arrays of temporary variables as well.  I am sure there are other ways, but these are the two that come to mind.

data countries(keep=country rename=(country=del));
   input country $ 1-20 Births Deaths;
   datalines;
USA                  15  9
Afghanistan          52 30
Algeria              50 16
Angola               47 23
Argentina            22 10
Australia            16  8
Austria              12 13
Bangladesh           47 19
Belgium              12 12
Brazil               36 10
Bulgaria             17 10
Burma                38 15
Cameroon             42 22
Canada               16  7
Chile                22  7
China                31 11
Taiwan               26  5
Colombia             34 10
Cuba                 20  6
Czechoslovakia       19 11
Ecuador              42 11
Egypt                39 13
Ethiopia             48 23
France               14 11
German Dem Rep       12 14
Germany, Fed Rep of  10 12
Ghana                46 14
Greece               16  9
Guatemala            40 14
Hungary              18 12
India                36 15
Indonesia            38 16
Iran                 42 12
Iraq                 48 14
Italy                14 10
Ivory Coast          48 23
Japan                16  6
Kenya                50 14
Korea, Dem Peo Rep   43 12
Korea, Rep of        26  6
Madagascar           47 22
Malaysia             30  6
Mexico               40  7
Morocco              47 16
Mozambique           45 18
Nepal                46 20
Netherlands          13  8
Nigeria              49 22
Pakistan             44 14
Peru                 40 13
Philippines          34 10
Poland               20  9
Portugal             19 10
Rhodesia             48 14
Romania              19 10
Saudi Arabia         49 19
South Africa         36 12
Spain                18  8
Sri Lanka            26  9
Sudan                49 17
Sweden               12 11
Switzerland          12  9
Syria                47 14
Tanzania             47 17
Thailand             34 10
Turkey               34 12
USSR                 18  9
Uganda               48 17
United Kingdom       12 12
Upper Volta          50 28
Venezuela            36  6
Vietnam              42 17
Yugoslavia           18  8
Zaire                45 18
;


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;
This is a USA line.
Here we have Yugoslavia and Zaire.
;   

proc print; run;
Contributor
Posts: 26

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

Posted in reply to WarrenKuhfeld

Hello, 

 

Thank you for your reply. 

Let me write what I understand. 

 

You first created a data set which contains only country names.  They were supposed to be country names you want to replace with space in the second data set. 

 

Sorry, but I could not understand what you did after that. 

Do you mind explaining it please? It looks that you created a different data set using a data set, 'countries'. 

 

Thank you, 

 

Yoko

SAS Super FREQ
Posts: 508

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

Personally, I would have just used emacs to construct all of the assignment statements.  However, I tried to answer your question within the parameters that you set.  So in the second DATA step I execute a TRANWRD assignment statement n times where n is the number of countries in the first data set.  You did not say what your data set looked like, so I just worked up something with two lines and a few country names.  POINT=I reads the ith row of a data set.  So I read one observation from X and then process all the rows in my country names data set.  Then I read the second observation from X and so on.

Contributor
Posts: 26

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

Posted in reply to WarrenKuhfeld

Thank you for taking time to explain the program to me. 

I now understand your method.  I also tried the program based on your explanation (I copied what I wanted tried based on your explanation below)  

 

 

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

 

This was what I wanted to see.   

 

Thank you!

 

Yoko

 

 

 

Super User
Posts: 10,844

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

options noquotelenmax;
data _null_;
  string='Canada, US, Japan, Korea, China';
  pos=prxchange('s/Canada|US/ /i',-1,string);
  put pos=;
run;
☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 316 views
  • 0 likes
  • 3 in conversation