Help using Base SAS procedures

Help dynamically selecting columns to assign values to a new column

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

Help dynamically selecting columns to assign values to a new column

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:

OFFSETTEMP_1TEMP_2TEMP_3
1123
3456
2789

Should have the following column added:

OFFSETTEMP_1TEMP_2TEMP_3NEW_COLUMN
11231
34566
27898

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


Accepted Solutions
Solution
‎03-12-2012 10:10 AM
Super User
Super User
Posts: 7,039

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

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;

View solution in original post


All Replies
Solution
‎03-12-2012 10:10 AM
Super User
Super User
Posts: 7,039

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

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;

Occasional Contributor
Posts: 7

Help dynamically selecting columns to assign values to a new column

Beauty Tom - that worked like a charm!

Thanks

Respected Advisor
Posts: 3,156

Help dynamically selecting columns to assign values to a new column

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

Regular Contributor
Posts: 233

Help dynamically selecting columns to assign values to a new column

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

Frequent Contributor
Posts: 127

Help dynamically selecting columns to assign values to a new column

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;

Super User
Posts: 10,023

Help dynamically selecting columns to assign values to a new column

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

Frequent Contributor
Posts: 127

Help dynamically selecting columns to assign values to a new column

Very interesting, thanks for sharing!

Occasional Contributor
Posts: 7

Help dynamically selecting columns to assign values to a new column

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!

Super User
Posts: 10,023

Help dynamically selecting columns to assign values to a new column

That is meaning nothing.

Just give it to TOM.

Actually I love TOM's code too.

Smiley Happy

Ksharp

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 224 views
  • 4 likes
  • 6 in conversation