DATA Step, Macro, Functions and more

Tranwrd on multiple variables within a loop

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

Tranwrd on multiple variables within a loop

[ Edited ]

Hi there,

 

I have the following code in SAS D.I studio.

 

My objective is to run multiple tranwrd statements (only 2 shown out of ~50) against a number of variables within a dataset. (Please note the Tranwrd 'replace' values have just been used for illustrative purposes).

 

options symbolgen mprint mlogic;


data table_x;
	input first_name :$10. location:$10. junk:$10.;
	datalines;
	Rober$t L$ee£ds A£BCD
	Chris£topher M$£chester A$$BCD
	Marti$n Y$o£rk ABC£D
;
run;


%macro bulk(column);

data staging_x;
	set table_x;
	&column. = tranwrd(&column.,'$','Y');
	&column. = tranwrd(&column.,'£','X');
run;

proc sql;
create table table_y as select *
	from &syslast.;
quit;

%mend bulk;


%macro run_now;

proc contents
	data = work.table_x;
	out = work.table_output;
quit;

proc sql noprint;
	select distinct NAME into %nrstr(:name1-:name999) from work.table_output;
quit;

data _null_;
	%do i=1 %to &sqlobs.;
	%let Var = &&name&i;
	%put Var = &Var.;

	%bulk(&Var.);

	%end
	
run;
;
%mend run_now;

%run_now;

 

The issue I'm having is every time the job completes it appears my Tranwrd statement has only been applied on one variable. For example;

 

Table_Y

 

first_name location    junk

 Rober$t   LYeeXds    A£BCD    
 Chris£top MYXchester A$$BCD   
 Marti$n   YYoXrk     ABC£D  

 

(The Tranwrd statement has been succesfully ran on the location variable).  

 

If I change the code which creates table Y from using a set statment to using a proc append it looks like the results of my tranwrd function are applied to each variable independently but I just can't manage to get the output into a single dataset.


All help very much appreciated


Accepted Solutions
Solution
‎05-17-2016 08:50 AM
SAS Super FREQ
Posts: 708

Re: Tranwrd on multiple variables within a loop

Hi

 

If you want to do the same operation on a number of varibales, I would use an array. See sample below.

 

data table_x;
	input first_name :$10. location:$10. junk:$10.;
	datalines;
	Rober$t L$ee£ds A£BCD
	Chris£topher M$£chester A$$BCD
	Marti$n Y$o£rk ABC£D
;
run;


data staging_x;
	set table_x;
  array _trancols{*} first_name location junk;
  do _i = 1 to dim(_trancols);
	_trancols{_i} = tranwrd(_trancols{_i},'$','Y');
	_trancols{_i} = tranwrd(_trancols{_i},'£','X');
  end;
run;

Bruno

View solution in original post


All Replies
Solution
‎05-17-2016 08:50 AM
SAS Super FREQ
Posts: 708

Re: Tranwrd on multiple variables within a loop

Hi

 

If you want to do the same operation on a number of varibales, I would use an array. See sample below.

 

data table_x;
	input first_name :$10. location:$10. junk:$10.;
	datalines;
	Rober$t L$ee£ds A£BCD
	Chris£topher M$£chester A$$BCD
	Marti$n Y$o£rk ABC£D
;
run;


data staging_x;
	set table_x;
  array _trancols{*} first_name location junk;
  do _i = 1 to dim(_trancols);
	_trancols{_i} = tranwrd(_trancols{_i},'$','Y');
	_trancols{_i} = tranwrd(_trancols{_i},'£','X');
  end;
run;

Bruno

Occasional Contributor
Posts: 12

Re: Tranwrd on multiple variables within a loop

Posted in reply to Bruno_SAS

Thanks so much for this, works a dream!

 

Just in case anyone has similar need here is my final code;

 

data table_x;
	input first_name :$10. location:$10. junk:$10.;
	datalines;
	Rober$t L$ee£ds A£BCD
	Chris£topher M$£chester A$$BCD
	Marti$n Y$o£rk ABC£D
;
run;


proc contents
	data = work.table_x
	out = work.table_output;
quit;

proc sql noprint;
	select distinct NAME into :varlist separated by " " from work.table_output;  
quit;


data staging_x;
set table_x;
  array _trancols{*} &varlist.;
  do _i = 1 to dim(_trancols);
	_trancols{_i} = tranwrd(_trancols{_i},'$','Y');
	_trancols{_i} = tranwrd(_trancols{_i},'£','X');
  end;
run;

 

I re-inserted the proc contents statement and created a parameter named varlist as some of the datasets I will eventually be running this code against will have a large number of variables.


Thanks again

SAS Super FREQ
Posts: 708

Re: Tranwrd on multiple variables within a loop

Hi

 

Glad it is working for you.

 

If you want to have all character variables be part of the array you can use the following statement:

array _trancols{*} _character_;

Please note, an array can only have variables of the same type.

 

Bruno

Super User
Posts: 7,758

Re: Tranwrd on multiple variables within a loop

Everytime the macro bulk is called, it will overwrite the previous staging_x and table_y with data from the original table_x, so only the last run will take effect.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 380 views
  • 2 likes
  • 3 in conversation