Creating multiple new variables from multiple existing variables

[va003]

Hello,

I am a new SAS user, and this is my first time posting a question. I did try to look for existing answers but I have not seen a similar question being asked. So maybe it's either not feasible or laughably simple. So please bare with me.

I have a table with a date variable, a "RF" (stands for "risk free") variable, and 20+ variables with these names:

small_lobm, me1_bm2, ,me1_bm3, me1_bm4, small_hibm, 

me2_bm1, me2_bm2, ,me2_bm3, me2_bm4, me2_bm5,

me3_bm1, me3_bm2, ,me3_bm3, me3_bm4, me3_bm5,

me4_bm1, me4_bm2, ,me4_bm3, me4_bm4, me4_bm5,

big_lobm, me5_bm2, ,me5_bm3, me5_bm4, big_hibm

(I break them into 5 lines just for the ease of reading so you can see how the names were constructed). So what I am trying to do is, for each of these 20+ variables, create a new column that is equal to variable - RF. Something like this:

Data want;
set have;
small_lobmx = small_lobm - RF;
run;

I try to see if it's possible to not have to do that same thing 20+ times for each of them. And I don't need to keep the old 20+ ones afterward. I'm using AS 9.4. Please help and thank you very much!

[Norman21]

Someone will suggest a way to do this in SAS, but thinking outside the box...

You can create lines of SAS code in Excel, simply by concatenating strings. One you have a column of text with the code you need, simply copy the cells from Excel, and paste them into SAS.

The result isn't "pretty", but it does work.

Oh, and welcome to the forum!

[va003]

Thank you for your response!

[FreelanceReinh · Accepted solution]

Hello @va003 and again welcome to the SAS Support Communities!

You can use arrays and a DO loop to perform the calculation "newvar = oldvar - RF" for all 20+ variables. While the list of the existing 20+ variables could be written as small_lobm--big_hibm (assuming that small_lobm is the first and big_hibm is the last of the 20+ variables in the dataset), we need another technique to construct the new variable names (with suffix "x"). One possible way to do this is to retrieve the variable names from dictionary table COLUMNS.

The PROC SQL step below creates a macro variable NEWNAMES containing the list of the 20+ new variable names, separated by blanks. For convenience, it also creates a macro variable OLDNAMES containing the list of the 20+ old variable names (so we don't need to use the list small_lobm--big_hibm😞

proc sql noprint;
select name, cats(name,'x') into :oldnames separated by ' ',
                                 :newnames separated by ' '
from dictionary.columns
where libname='WORK' & memname='HAVE' & upcase(name) not in ('DATE' 'RF')
order by varnum;
quit;

data want;
set have;
array v [*] &oldnames;
array vx[*] &newnames;
do i=1 to dim(v);
  vx[i]=v[i]-rf;
end;
drop i &oldnames; 
run;

When you adapt the above code to your real dataset, make sure to:

• replace "HAVE" by the name of your dataset (upper case!) in the WHERE clause of the PROC SQL step
• replace "WORK" by the name of the library where it resides (upper case!)
• replace "DATE" by the name of your date variable (again, upper case)
• insert more quoted upper-case variable names into the list ('DATE' 'RF') if the date variable and RF are not the only variables to be excluded from the transformation.

[va003]

Wow that's a lot going on! Thank you very much for your help!