Copying/renaming multiple sas datasets based on macrovariables values

2
votes

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!

2
sassas-macrodatastep
If you didn't have the macro variables what SAS code would you use to create the datasets you want? Once you know the code you want to generate then it is easier to use macro logic to generate it. – Tom

2 Answers

3
votes

I would define my macro variable slightly differently and do something like this:

%let oldnames = CDR_2016jun Cog_2016jun Mile_2016jun Path_2016jun;
%let newnames = CDR Cog Mile Path;

proc datasets lib = cpo noprint;
    copy out = work;
    select &oldnames;
    run;
quit;

%macro changes;
%local i;
%do i = 1 %to %sysfunc(countw(&oldnames));
    %scan(&oldnames, &i, %str( )) = %scan(&newnames, &i, %str( ))
%end;
%mend changes;

proc datasets lib = work noprint;
    change %changes;
    run;
quit;

Alternatively, you could create views in work of the original datasets in cpo.

2
votes

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.