BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mlcross
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
BrunoMueller
SAS Super FREQ

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

4 REPLIES 4
BrunoMueller
SAS Super FREQ

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

mlcross
Calcite | Level 5

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

BrunoMueller
SAS Super FREQ

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

Kurt_Bremser
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 3403 views
  • 3 likes
  • 3 in conversation