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!

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, Aug 12 '16 at 16:16

score 3 · Answer 1 · answered Aug 12 '16 at 16:16

3

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.

answered Aug 12 '16 at 16:16

user667489

9,501
2
24
35

I appreciate your answer, I'm not familiar enough with proc datasets to use this right now but will look into it. I would give you an upvote if my reputation was high enough. – RAND Aug 12 '16 at 16:29
@RMontgomery If you need renaming data sets `proc datasets` is the fastest way. If copy then `proc copy` or `proc datasets` with `copy` statement. If implement some logic during rewriting then `data step`. But with small data sets like you have there is no need to focus on that. Just choose method that fits you best. – fl0r3k Aug 12 '16 at 16:50
@fl0r3k thanks for the info, I'm reading about proc datasets now. I will be using this code on much bigger data sets in order to streamline a current process we have so any extra efficiency might be preferable. I'll have to keep reading, thanks! – RAND Aug 12 '16 at 16:59

score 2 · Accepted Answer · edited Aug 12 '16 at 16:39

2

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.

edited Aug 12 '16 at 16:39

Robert Penridge

8,424
2
34
55

answered Aug 12 '16 at 16:21

fl0r3k

619
5
9

This works with the small edit that in the macro %revriteDatasets &myfiles and &New_Datasets should be switched. Thanks! – RAND Aug 12 '16 at 16:27
I like this generally, but you should not use global macro variables. Pass `&myfiles` and `&new_datasets` as parameters. – Joe Aug 12 '16 at 16:33
I've updated the code a little to make it simpler to see how it works (for those less experienced with macros), and I've added in some basic error checking as well. – Robert Penridge Aug 12 '16 at 16:40
Off topic but @RobertPenridge why is `ii` better than `i`?? Assuming you use `local` it shouldn't matter what you use, should it? (personally I like `_i` because I use `_` to prefix temporary variables like iterators, but I don't see how adding a second `i` has even that advantage?) – Joe Aug 12 '16 at 18:58
1

@Joe I moved away from using `i`,`j`,`k`, etc. as temporary variables as they're difficult to search on. `ii`,`jj`,`kk` is almost as readable and still allows you to find them using search. I tried a variety of other things such as `cnt`, `cnt1`, and so on, but I felt that while they allowed searching, they reduced readability. – Robert Penridge Aug 12 '16 at 19:45
@Joe I try to avoid using `_` as a prefix in SAS as I've been burnt by some of SAS's undocumented 'features'. From memory I was working with the SAS XML mapper and it was something to do with either fields named `_temp` or tables named `_temp`. I guess SAS used them internally somewhere. I'd have to dig around to find it, but generally when I come across stuff like that I adopt practices to avoid running into similar things in future. – Robert Penridge Aug 12 '16 at 19:46
@RobertPenridge You are right about `_`. There can be hidden/undocumented features or options with `_` at the begining. For instance in `proc sql` you have `_method` or `_tree` options. In case of macrovariables I don't think thay are hidden. All macrovariables can be displayed with `%put _all_;` or quering `dictionary.macros`. But still think very nice practise avoiding `_` and using `ii`/`jj` instead. – fl0r3k Aug 12 '16 at 21:11

Copying/renaming multiple sas datasets based on macrovariables values

2 Answers2