Run a piece of code over different tables

[NewUsrStat]

Hi guys, 

suppose to have the following dataset: 

   data Tab3 (drop=value myperc_ label2);
	  set Tab3_;
	    mycount = input(scan(value , 1, ' ('), best.);
	    myperc_ = scan(value , 2, '()');
	    perc = compress(myperc_ , ' %');
	    label = label2;
run;

With these few lines of code I split value from [N(%)] to [N], [%] as two different variables instead of one.

I have to do this for 30 tables that are generated by a macro I cannot modify because of company policies. Is there a way to perform this task once across multiple tables, rather than editing each table individually?

One note: the output tables are stored in my working dir.

Thank you in advance. 

[Kurt_Bremser · Accepted solution]

Wrap it in a macro:

%macro change_it(tabname);
data &tabname. (drop=value myperc_ label2);
set &tabname._;
mycount = input(scan(value , 1, ' ('), best.);
myperc_ = scan(value , 2, '()');
perc = compress(myperc_ , ' %');
label = label2;
run;
%mend;

%change_it(tab3) /* and so on */

[sbxkoenk]

Alternative for a macro.

Dynamically write a program (tt) that contains all the datasteps you need. Then submit this program with %INCLUDE.

%LET ListDatasets=AA BB CC DD EE FF GG HH;

filename tt temp;

data _NULL_;
 file tt;
 i=1;
 do while ( scan("&ListDatasets",i," ") ne " " );
  currds=trim(left(upcase(scan("&ListDatasets",i," "))));

   put "data " currds "(drop=value myperc_ label2);    ";
   put " set " currds +(-1) "_;                        ";
   put " mycount = input(scan(value , 1, ' ('), best.);";
   put " myperc_ = scan(value , 2, '()');              ";
   put " perc = compress(myperc_ , ' %');              ";
   put " label = label2;                               ";
   put "run;                                           ";

  i=i+1;
 end;
run;

%INCLUDE tt / source2;
/* end of program */

BR, Koen

[Tom]

Do the datasets all have the same structure? Do you want to output one single dataset that contains the observations from all of the datasets?  Or do you need to keep them separate?

Do you know the names of the datasets? 

If not then how will you get the names?  Do you need to run this step on every dataset that has the variable VALUE?

Is there a reason you are leaving PERC as character but converting N to a number?

Is there another variable with the overall N that you could use to re-calculate PERC instead of just reading in the rounded value that was written into the character variable VALUE?

NOTE:  BEST is the name of a FORMAT.  Your code works because SAS treats BEST when used as an INFORMAT as an alias for the normal numeric informat.