BookmarkSubscribeRSS Feed
tucson77
Calcite | Level 5

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

 

 

7 REPLIES 7
Norman21
Lapis Lazuli | Level 10

You could try using the substring function.

 

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

 

Norman.

Norman.
SAS 9.4 (TS1M6) X64_10PRO WIN 10.0.17763 Workstation

mohamed_zaki
Barite | Level 11

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

Astounding
PROC Star

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.

stat_sas
Ammonite | Level 13

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;

tucson77
Calcite | Level 5

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

 

Gekrepten
Calcite | Level 5

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

FreelanceReinh
Jade | Level 19

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 7 replies
  • 1166 views
  • 0 likes
  • 7 in conversation