topic Re: Substitute Characters in a String by multiple values and converting those values into individual in SAS Programming

Substitute Characters in a String by multiple values and converting those values into individual row

rosegarden81 — Tue, 28 Aug 2018 21:24:43 GMT

Hello guys,

I am trying to convert a variable say "747.x" into 747.0,747.1,747.2---747.9 and place them in subsequent rows.

Like for example, my original dataset

Var

747.x

056.xx

I want the dataset to look like

Converted
747.0'
747.1
747.2
747.3
747.4
747.5
747.6
747.7
747.8
747.9
056.00'
056.01'
056.02'
.
.
056.99'

I can use TRNWRD to replace .x with .0 or 0.1 etc but if there is an efficient way to convert .x into .0, 0.1...0.9 and place them into rows, that would be helpful!

Regards,

Tina

Re: Substitute Characters in a String by multiple values and converting those values into individual

novinosrin — Tue, 28 Aug 2018 22:30:03 GMT

data have;
input Var $;
cards;
747.x
056.xx
;

data want;
set have;
_var=input(tranwrd(var, "x", "0"),8.);
_start=1/10**(length(scan(var,-1)));
_stop=1-_start;
 want_var=_var;
output;
do _n=_start to _stop by _start;
 want_var=_var+_n;
output;
end;
keep var want_var;
run;

Re: Substitute Characters in a String by multiple values and converting those values into individual

ChrisNZ — Tue, 28 Aug 2018 22:36:25 GMT

Like this?

data WANT;
  set HAVE;
  MAXNB=10**(length(scan(VAR,2)))-1;
  do I=0 to MAXNB;
    VAR=catt(scan(VAR,1),'.',putn(I,catt('z',length(cat(MAXNB)),'.')));
    output;
  end; 
run;

VAR
747.0
747.1
747.2
747.3
747.4
747.5
747.6
747.7
747.8
747.9
056.00
056.01
056.02
056.03
056.04

Re: Substitute Characters in a String by multiple values and converting those values into individual

ballardw — Tue, 28 Aug 2018 22:36:06 GMT

See if this gets you started:

data want;
   length var $ 8;
   input var $;
   numx = countc(scan(var,2,'.'),'x');
   if numx>0 then do i=0 to (10**numx - 1);
      select (numx);
         when (1) fmt='z1.';
         when (2) fmt='z2.';
         when (3) fmt='z3.';
         otherwise;
      end;
      var = catx('.',scan(var,1,'.'),putn(i,fmt));
      output;
   end;
   drop i numx fmt;
datalines;
747.x 
056.xx 
;
run;

This example will not work if you have a value like 123.1x, only if all the values after the decimal are x's. Considerations may be needed if the case of the x are inconsistent. I assumed lower case as per your example but adding a lowcase may be needed.

This will not replace X before the decimal if such occurs.

It will handle 3 x's if they appear.

Re: Substitute Characters in a String by multiple values and converting those values into individual

Astounding — Wed, 29 Aug 2018 04:15:09 GMT

Why go through that trouble? You can always locate the values you want using:

if ('747.' < varname < '748.');

Re: Substitute Characters in a String by multiple values and converting those values into individual

rosegarden81 — Wed, 29 Aug 2018 17:07:36 GMT

I really appreciate all of your answers guys! I do have some values like 720.1x too; I found out a way to work with them using some macro coding but will try to understand the code here, and implement those for .1x type codes too. Will let you know how it goes. This is very helpful!

Re: Substitute Characters in a String by multiple values and converting those values into individual

Astounding — Wed, 29 Aug 2018 19:55:05 GMT

The same approach still works:

if ('720.1' < varname < '720.2');