SAS - Creating variables from macro variables

Question

I have a SAS dataset which has 20 character variables, all of which are names (e.g. Adam, Bob, Cathy etc..)

I would like a dynamic code to create variables called Adam_ref, Bob_ref etc.. which will work even if there a different dataset with different names (i.e. don't want to manually define each variable).

So far my approach has been to use proc contents to get all variable names and then use a macro to create macro variables Adam_ref, Bob_ref etc..

How do I create actual variables within the dataset from here? Do I need a different approach?

proc contents data=work.names 
               out=contents noprint;
run;

proc sort data = contents; by varnum; run;

data contents1;
  set contents;
  Name_Ref = compress(Name||"_Ref");
  call symput (NAME, NAME_Ref); 
  %put _user_;
run;

Answer 1

If you want to create an empty dataset that has variables named like some values you have in a macro variables you could do something like this.

Save the values into macro variables that are named by some pattern, like v1, v2 ...

proc sql;
select compress(Name||"_Ref") into :v1-:v20 from contents;
quit;

If you don't know how many values there are, you have to count them first, I assumed there are only 20 of them.

Then, if all your variables are character variables of length 100, you create a dataset like this:

%macro create_dataset;
data want;
length %do i=1 %to 20; &&v&i $100 %end;
;
stop;
run;
%mend;

%create_dataset; run; 

This is how you can do it if you have the values in macro variable, there is probably a better way to do it in general.

If you don't want to create an empty dataset but only change the variable names, you can do it like this:

proc sql;
select name into :v1-:v20 from contents;
quit;

%macro rename_dataset;
data new_names;
set have(rename=(%do i=1 %to 20; &&v&i = &&v&i.._ref %end;));
run;
%mend;

%rename_dataset; run;

Answer 2

You can use PROC TRANSPOSE with an ID statement.

This step creates an example dataset:

data names;
    harry="sally";
    dick="gordon";
    joe="schmoe";
run;

This step is essentially a copy of your step above that produces a dataset of column names. I will reuse the dataset namerefs throughout.

proc contents data=names out=namerefs noprint;
run;

This step adds the "_Refs" to the names defined before and drops everything else. The variable "name" comes from the column attributes of the dataset output by PROC CONTENTS.

data namerefs;
    set namerefs (keep=name);
    name=compress(name||"_Ref");
run;

This step produces an empty dataset with the desired columns. The variable "name" is again obtained by looking at column attributes. You might get a harmless warning in the GUI if you try to view the dataset, but you can otherwise use it as you wish and you can confirm that it has the desired output.

proc transpose out=namerefs(drop=_name_) data=namerefs;
  id name;
run;

Answer 3

Here is another approach which requires less coding. It does not require running proc contents, does not require knowing the number of variables, nor creating a macro function. It also can be extended to do some additional things.

Step 1 is to use built-in dictionary views to get the desired variable names. The appropriate view for this is dictionary.columns, which has alias of sashelp.vcolumn. The dictionary libref can be used only in proc sql, while th sashelp alias can be used anywhere. I tend to use sashelp alias since I work in windows with DMS and can always interactively view the sashelp library.

proc sql;
  select compress(Name||"_Ref") into :name_list
                                separated by ' '
    from sashelp.vcolumn
   where libname = 'WORK' 
     and memname = 'NAMES';
quit;

This produces a space delimited macro vaiable with the desired names.

Step 2 To build the empty data set then this code will work:

Data New ;
  length &name_list ;
run ;

You can avoid assuming lengths or create populated dataset with new variable names by using a slightly more complicated select statement.

For example

 select compress(Name)||"_Ref $")||compress(put(length,best.)) 
                 into :name_list
                 separated by ' '

will generate a macro variable which retains the previous length for each variable. This will work with no changes to step 2 above.

To create populated data set for use with rename dataset option, replace the select statement as follows:

 select compress(Name)||"= "||compress(_Ref") 
                   into :name_list
                   separated by ' '

Then replace the Step 2 code with the following:

Data New ;
  set names (rename = ( &name_list)) ;
run ;