SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

modifying variables

Reply
New Contributor
Posts: 2

modifying variables

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

 

 

Frequent Contributor
Posts: 95

Re: modifying variables

You could try using the substring function.

 

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

 

Norman.

Norman.
SAS 9.4 (TS1M4) X64_7PRO WIN 6.1.7601 Service Pack 1 Workstation

Super Contributor
Posts: 490

Re: modifying variables

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

Super User
Posts: 5,505

Re: modifying variables

[ Edited ]

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.

Trusted Advisor
Posts: 1,228

Re: modifying variables

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;

New Contributor
Posts: 2

Re: modifying variables

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

 

New User
Posts: 1

Re: modifying variables

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>)

Trusted Advisor
Posts: 1,117

Re: modifying variables

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.

Ask a Question
Discussion stats
  • 7 replies
  • 437 views
  • 0 likes
  • 7 in conversation