BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
TRCWard
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

9 REPLIES 9
Tom
Super User Tom
Super User

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
Calcite | Level 5

Beauty Tom - that worked like a charm!

Thanks

Haikuo
Onyx | Level 15

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
Obsidian | Level 7

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
Quartz | Level 8

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
Super User

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
Quartz | Level 8

Very interesting, thanks for sharing!

TRCWard
Calcite | Level 5

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
Super User

That is meaning nothing.

Just give it to TOM.

Actually I love TOM's code too.

Smiley Happy

Ksharp

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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