Help dynamically selecting columns to assign values to a new column

[TRCWard]

Hi - I'm relatively new to SAS (I've some experience with enterprise guide, but next to non with coding) and this is my first post.

So here's my problem: I have a data table with around 60,000 rows.  I need to create a new column in the table.  The values for this new column can be in any of 22 other columns (sequentially named TEMP_1 - TEMP_22).  A seperate column, OFFSET, contains a value between 0 and 22 that indicates which of the TEMP_ columns to pick a value from (0 means not to pick a value from any of the TEMP_ columns).  All of the columns have numerical values.

So, for example the table:

OFFSET	TEMP_1	TEMP_2	TEMP_3
1	1	2	3
3	4	5	6
2	7	8	9

Should have the following column added:

OFFSET	TEMP_1	TEMP_2	TEMP_3	NEW_COLUMN
1	1	2	3	1
3	4	5	6	6
2	7	8	9	8

I've tried using the following proc sql to assign values, but keep getting an error in the log.

     proc sql;

          create table Work.New_Table as

          select *,

          Case

               When OFFSET >= 1 then TEMP_||Left(put(offset(COLUMN_X, 12.))

               Else ""

               End As COLUMN_NEW

          From Work.Source_Table;

     quit;

LOG ERROR: The following columns were not found in the contributing tables: Temp_.

[Tom · Accepted solution]

Much easier to do with a SAS data step as you can use the ARRAY statement.

data new_table;
   set source_table;
   array temp temp_1-temp_22;
   if 1 <= offset <= dim(temp) then column_new=temp(offset);
run;

[TRCWard]

Beauty Tom - that worked like a charm!

Thanks

[Haikuo]

Array() seems to have a straigh shot on this one:

data have;

infile cards;

input OFFSET     TEMP_1     TEMP_2     TEMP_3;

cards;

1     1     2     3

3     4     5     6

2     7     8     9

;

data want;

set have;

array tmp (*) temp_1-temp_3;

new_column=tmp(offset);

run;

proc print;run;

Regards,

Haikuo

[Hima]

Code:

DATA TEMP;
INPUT OFFSET TEMP_1 TEMP_2 TEMP_3;
DATALINES;
1 1 2 3
3 4 5 6
2 7 8 9
;
RUN;

PROC SQL;
SELECT *, CASE WHEN OFFSET = 1 THEN TEMP_1
      WHEN OFFSET = 2 THEN TEMP_2
      WHEN OFFSET = 3 THEN TEMP_3
    ELSE . END AS COLUMN_NEW
FROM TEMP
;
QUIT;
ENDRSUBMIT;

                         The SAS System         10:08 Monday, March 12, 2012   1

                         OFFSET    TEMP_1    TEMP_2    TEMP_3  COLUMN_NEW
                       ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ
                              1         1         2         3           1
                              3         4         5         6           6
                              2         7         8         9           8

[Florent]

Another solution using the "vvaluex" function available with SAS 9:

data new_table(drop=TEMP_0);

set source_table;

TEMP_0 = .;

new_column = input(vvaluex(compress('TEMP_'||input(offset, best12.))), best12.);

run;

[Ksharp]

Oh. SAS has already offered a function to handle this problem.

data have;
infile cards;
input OFFSET     TEMP_1     TEMP_2     TEMP_3;
cards;
1     1     2     3
3     4     5     6
2     7     8     9
;
run;
data want;
 set have;
 column_new=choosen(offset,of temp:);
run;

Ksharp

[Florent]

Very interesting, thanks for sharing!

[TRCWard]

Thanks - nice to see that SAS already has a function for doing this.  Only now I'm not sure who to give "correct answer" to!

[Ksharp]

That is meaning nothing.

Just give it to TOM.

Actually I love TOM's code too.

Ksharp