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;