topic Help dynamically selecting columns to assign values to a new column in SAS Procedures

Help dynamically selecting columns to assign values to a new column

TRCWard — Mon, 12 Mar 2012 14:05:51 GMT

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_.

Re: Help dynamically selecting columns to assign values to a new column

Tom — Mon, 12 Mar 2012 14:10:07 GMT

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;

Help dynamically selecting columns to assign values to a new column

Haikuo — Mon, 12 Mar 2012 14:12:14 GMT

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

Help dynamically selecting columns to assign values to a new column

Hima — Mon, 12 Mar 2012 14:12:21 GMT

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

Help dynamically selecting columns to assign values to a new column

TRCWard — Mon, 12 Mar 2012 14:16:41 GMT

Beauty Tom - that worked like a charm!

Thanks

Help dynamically selecting columns to assign values to a new column

Florent — Mon, 12 Mar 2012 18:37:33 GMT

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;

Help dynamically selecting columns to assign values to a new column

Ksharp — Tue, 13 Mar 2012 02:55:15 GMT

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

Help dynamically selecting columns to assign values to a new column

Florent — Tue, 13 Mar 2012 13:12:48 GMT

Very interesting, thanks for sharing!

Help dynamically selecting columns to assign values to a new column

TRCWard — Tue, 13 Mar 2012 13:14:31 GMT

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!

Help dynamically selecting columns to assign values to a new column

Ksharp — Wed, 14 Mar 2012 03:05:34 GMT

That is meaning nothing.

Just give it to TOM.

Actually I love TOM's code too.

Ksharp