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;