Copying/renaming multiple sas datasets based on macrovariables values

Question

I have a macro variable &myfiles which contains a list of the names of four datasets.

%put &myfiles;
cpo.CDR_2016jun cpo.Cog_2016jun cpo.Mile_2016jun cpo.Path_2016jun

Where cpo is a libname.

I'm trying to create four new datasets with names from another macro variable which I've named &New_Datasets:

%put &New_Datasets;
CDR Cog Mile Path

I tried to just use a data step like this:

data &New_Datasets;
     set &myfiles;
run;

but that resulted in all of the observations of the four data sets referenced in &mylist being combined and put into each of the four data sets referenced in &New_Datasets, with the following output from the log:

NOTE: There were 1482 observations read from the data set CPO.CDR_2016JUN.
NOTE: There were 1444 observations read from the data set CPO.COG_2016JUN.
NOTE: There were 255 observations read from the data set CPO.MILE_2016JUN.
NOTE: There were 7 observations read from the data set CPO.PATH_2016JUN.
NOTE: The data set WORK.CDR has 3188 observations and 1580 variables.
NOTE: The data set WORK.COG has 3188 observations and 1580 variables.
NOTE: The data set WORK.MILE has 3188 observations and 1580 variables.
NOTE: The data set WORK.PATH has 3188 observations and 1580 variables.

What I want to accomplish is to have the 1482 observations from cpo.cdr_2016jun create a data set work.cdr with 1482 observations and so on, rather than having each of the new data sets be a combination of the ones referenced in the set statement. Any help would be greatly appreciated, thanks!

Answer 1

You must write a macro program that loops through values in your macrovariables and calls data step or proc copy.

Macro:

%macro rewriteDataSets(source_tables=, dest_tables=);
   %local ii num_source_tables num_dest_tables source_name dest_name;

   %let num_source_tables = %sysfunc(countw(&source_tables, %str( )));
   %let num_dest_tables   = %sysfunc(countw(&dest_tables  , %str( )));

   %if &num_source_tables ne &num_dest_tables %then %do;
      %put ERROR: The number of source and destination tables must be the same in the call to rewriteDataSets;
      %abort cancel;
   %end;

   %do ii=1 %to &num_source_tables;

      %let source_name = %scan(&source_tables, &ii, %str( ));
      %let dest_name   = %scan(&dest_tables  , &ii, %str( ));

      data &dest_name;
        set &source_name;
      run;

   %end;
%mend rewriteDataSets;

Example Usage:

%rewriteDataSets(source_tables = sashelp.class sashelp.class,
                 dest_tables   = a b);

Or using the tables that you specified, you would call it like this:

%rewriteDataSets(source_tables = cpo.CDR_2016jun cpo.Cog_2016jun cpo.Mile_2016jun cpo.Path_2016jun,
                 dest_tables   = CDR Cog Mile Path);

Or use proc copy instead of data step.