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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.