How to Replace Variables in a Work.Library Table

[JibJam221]

Hi everyone, 

im looking for an efficient way to replace values in a table in a SAS work.library. This data was imported from excel, and there are many variables in multiple columns that im looking to change. For example:

in column1, column2, column3, I would like to replace their values from "somewhat negative" and "extremely negative" to simply "negative".

Any ideas? 

thanks!

[Kurt_Bremser]

You can use 

column1 = scan(column1,-1);

to keep the last word.

[JibJam221]

thank you, but i'd definitely need a way to replace possibly using an if-then, since some responses come up as "neutral" as well.

[ballardw]

If the values are consistent and especially if the values are the same in multiple variables a custom format might be a more flexible approach and does not actually require changing values at all.

A brief example:

Data have;
   input v1 $ v2 $;
datalines;
word1  word2
word3  word4
;

proc format;
value $mywords
'word1' = 'Nicer meaning 1'
'word2' = 'Nicer meaning 2'
'word3' = 'Nicer meaning 3'
'word4' = 'Nicer meaning 4'
;
run;

proc print data=have;
   format v1 v2 $mywords.;
run;

A format consists of a value range and a display shown above. The $ is needed in the name of the custom format in the Value statement for character values. In the above example the longer text is displayed in the Print output instead of the simple 'word1' values. If there had been 50 variables recorded with the same coding scheme then the format could be applied to all of them. No "if/then/else" needed. No chance of damaging values in a data step from poor logic or typos. And when you get to terabyte sized data sets the time just to change the values adds up.

One extremely nice bit by using a format is that you can have multiple formats used with the same values, such as one format that shows a 5-level "agreement" and another that combines "Agree strongly" and "somewhat agree" into a simple "Agree" and similar with the disagree side creating 3 levels (assumes some sort of 'neutral' or 'neither agree/disagree' in the middle). The formats when applied in an analysis procedure like Proc Freq would then count based on which is the current format, or use in graphing procedures to create barcharts based on the format, or in most of the SAS analysis procedures.

You do have to make sure the format is available to the current SAS session.

[Reeza]

Here's a tutorial on using Arrays in SAS
https://stats.idre.ucla.edu/sas/seminars/sas-arrays/

You need arrays to process multiple variables in the same fashion.

data want;
set have;
array myvars(3) col1-col3;


do i=1 to dim(myvars);

if myvars(i) in ('somewhat negative', 'extremely negative') then myvars(i) = 'negative';

end;

run;

---

@JibJam221 wrote:

Hi everyone, 

im looking for an efficient way to replace values in a table in a SAS work.library. This data was imported from excel, and there are many variables in multiple columns that im looking to change. For example:

in column1, column2, column3, I would like to replace their values from "somewhat negative" and "extremely negative" to simply "negative".

Any ideas? 

thanks!

---