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_.
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;
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;
Beauty Tom - that worked like a charm!
Thanks
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
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
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;
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
Very interesting, thanks for sharing!
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!
That is meaning nothing.
Just give it to TOM.
Actually I love TOM's code too.
Ksharp
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.