topic Tranwrd on multiple variables within a loop in SAS Programming

Tranwrd on multiple variables within a loop

mlcross — Tue, 17 May 2016 11:13:26 GMT

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

Re: Tranwrd on multiple variables within a loop

BrunoMueller — Tue, 17 May 2016 11:28:02 GMT

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

Re: Tranwrd on multiple variables within a loop

Kurt_Bremser — Tue, 17 May 2016 11:28:25 GMT

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.

Re: Tranwrd on multiple variables within a loop

mlcross — Tue, 17 May 2016 12:53:04 GMT

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

Re: Tranwrd on multiple variables within a loop

BrunoMueller — Wed, 18 May 2016 06:31:56 GMT

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