topic Re: modifying variables in SAS Data Management

modifying variables

tucson77 — Sun, 17 Jan 2016 09:24:34 GMT

Guys,

i have the following dataset:

Variabe1 Variabe2 Variabe3 Variabe4

ND1 ND2 ND5 ND6

ND1 ND1 ND3 ND4

ND3 ND6 ND5 ND2

I would like to change all these values from ND1 ND2 ND3 ND4 ND5 ND6 to ND,1 ND,2 ND,3 ND,4 ND,5 and ND,6.

In summary I would like to add a comma before the number.

Your help will greatly be appreciated.

Thx

Re: modifying variables

Norman21 — Sun, 17 Jan 2016 10:53:20 GMT

You could try using the substring function.

See http://www2.sas.com/proceedings/forum2007/217-2007.pdf

Norman.

Re: modifying variables

mohamed_zaki — Sun, 17 Jan 2016 13:24:13 GMT

Easier you can use PROC FORMAT, like

proc format;
value  $myFormat 
      	           'ND1'='ND,1'
                   'ND2'='ND,2'
                   'ND3'='ND,3'
                   'ND4'='ND,4'
                   'ND5'='ND,5'
                   'ND6'='ND,6';
run;

Then assign it to your variables

Re: modifying variables

Astounding — Sun, 17 Jan 2016 19:52:43 GMT

First step would be to make sure your variables have a long enough length to hold an extra character. Then SUBSTR can change the value. Here would be one way (just illustrated for a single variable):

data want;

set have;

if variable1 =: 'ND' then variable1='ND,' || scan(variable1,2,'D');

run;

Based on your latest comments, I changed the code in 2 ways. The LENGTH statement is gone, and the comparison now checks to see whether the original value begins with "ND". It would still need to be expanded (using arrays) to cover the additional variables that you want to check.

Re: modifying variables

stat_sas — Sun, 17 Jan 2016 18:30:06 GMT

data want(drop=i);
set have;
array v(*) v:;
do i=1 to dim(v);
v(i)=cats('ND,',compress(v(i),'','kd'));
end;
run;

Re: modifying variables

tucson77 — Sun, 17 Jan 2016 19:31:42 GMT

Thanks guys for your answers, much appreciated.

My project is a bit more complex. Actually ND means 'no data' and the suffix are just the different levels of missingness (not very important here though). So these variables will later be populated.

here is what I have

Variabe1_Int_rate Variable2_Orig_date Variable3_Purpose Variable4_Arrears

9.2 ND1 Unsecured 169000

0.65 1997-07 Secured ND3

2003-11 Unsecured 259231

0.65 ND3 Unsecured ND5

ND4 ND6 Secured ND2

Below is what i want:

Variabe1_Int_rate Variable2_Orig_date Variable3_Purpose Variable4_Arrears

9.2 ND,1 Unsecured 169000

0.65 1997-07 Secured ND,3

2003-11 Unsecured 259231

0.65 ND,3 Unsecured ND,5

ND,4 ND,6 Secured ND,2

As before i would like to replace all ND by ND, (example ND1 becomes ND,1 etc).

Thanks in advance for your help

Re: modifying variables

Gekrepten — Sun, 17 Jan 2016 20:09:09 GMT

You could use the function TRANWRD, pls see an example:

data sastest;
x = 'ND209';
y= tranwrd(x,'ND','ND,');
put x;
put y;
run;

TRANWRD(Variable, <string to substitute>,<new string>)

Re: modifying variables

FreelanceReinh — Sun, 17 Jan 2016 20:50:27 GMT

Have you considered using special missing values (.a, .b, ..., .z, ._)? With these you could distinguish between up to 27 "different levels of missingness" in addition to the familiar missing value (.). At the same time you could perform calculations with the non-missing values of these variables and handle missing values with common functions such as MISSING(). This suggestion would apply only to numeric variables, though. Currently, your variables must be character, but the values shown for three of them suggest that numeric variables might be an option.