extend list to include factor transforms in SAS

Question

I have a dataset like this

data have;
input ID Label;
datalines;
Factor_1 Assets
Factor_2 Liabilities
Factor_3 Bonds
;
run;

I'm looking to create a new dataset to accomodate a factor transformation and I will need to update my dictionary as such

data want;
input ID Label;
datalines;
Factor_1_log Assets_log
Factor_1_sq Assets_sq
Factor_2_log Liabilities_log
Factor_2_sq liabilities_sq
;
run;

I have tried this so far

data want;
set have;
by ID;
if first.ID then do;
output;
ID = ID||_log;
output;
ID = ID||_sq;
output;
end;
run;

But to no avail, is there a way to extend my list and append the correct phrase?

Answer 1

First thing is that string literals need to be in quotes. So you want to use '_sq' instead of _sq. Without the quotes SAS will assume that _sq is the name of a variable.

Also SAS character variables are fixed length so using the simple concatenation operator || append the suffix after the spaces. Then trying to write the result back into the same variable will lose the suffix since it will not fit into variable.

You do not need to use BY processing for this. You are expanding each row into multiple rows.

data want;
  set have;
  output;
  length suffix $8 ;
  do suffix='log','sq';
    id = catx('_',id,suffix);
    label = catx('_',label,suffix);
    output;
  end;
  drop suffix;
run;

If you don't want to keep the original observations then remove the output statement before the do loop.

Answer 2

@Tom way is the best, just another way to do in SQL

proc sql;
create table want as 
 select cats(id,newcol) as id, cats(label,newcol) as label from 
       (select * from have)a
        cross join
       (select distinct '_log' as newcol from have
       union 
       select distinct '_sq' as newcol from have)b;