Concatenate with CATX

[Jcorti]

Hi All,

Someone of you have you ever concatenate a variable from another dataset?

I want to concatenate char values like a., b., c. until x.  with a range previously defined (combination of r1 and r2 variables)

The issue that I am getting is with "r" variable that I want to concatenate with "r1" and "r2".

Mu dataset has 2500 observation and variable "range" has 25 ranges --That´s why I am doing this on my code because the range is repeating 100 times

do j=1 to 100;

Any suggestion on that??

Thanks,

%macro concat;
data testa (keep=i) ;
do j=1 to 100;
do i='a.','b.','c.','d.','e.','f.','g.','h.','i.','j.','k.','l.',
'm.','n.','o.','p.','q.','r.','s.','t.','u.','v.','w.','x.','y.';
   %let j=i;
output;
end;
end;
run;
data test (drop=r1 r2 "Range Start"n "Range End"n) ;
retain month_var Range "Current Count"n "Current %"n " Previous Count"n "Previous %"n "% Change"n ;
set output.&cons;
r1=put(input("Range Start"n,15.),comma12.);
r2=put("Range End"n,comma15.);
r=&j.
Range=CATX('_',r,r1,r2); 
run;
%mend concat;
%concat;

[PGStats]

Why not extract a substring from "a._b._....x." ?

[Shmuel]

What do you mean by %let j=i; in first step ?

if you add %put J= &j; after first run you'll find in log that J=i as a constant.

did you mean:  j=i in order to save the sequence number or

call symput('j',i) which will keep the last value of i, i.e. 100.

the r=&j in your code result in r='i'  or if you use call symput you will get r=100.

I suppose you didn't mean neither of the two.

Please give an example of:

r1 = 'Range Start'n = 100;

r2 = 'Rabge End'n = 200;

what shold be r ? what is the connection between first step and the others ?

what result of catx you expect ?

[Jcorti]

What do you mean by %let j=i; in first step ?

Iterates 100 times from a: to x:

if you add %put J= &j; after first run you'll find in log that J=i as a constant.

did you mean:  j=i in order to save the sequence number or

call symput('j',i) which will keep the last value of i, i.e. 100.

the r=&j in your code result in r='i'  or if you use call symput you will get r=100.

I suppose you didn't mean neither of the two.

Please give an example of:

r1 = 'Range Start'n = 100;

r2 = 'Rabge End'n = 200;

what shold be r ? what is the connection between first step and the others ?

what result of catx you expect ?

This is the example using 

Range=CATX('_',r1,r2);

._0

1_100

101_500

501_1,000

1,001_1,500

1,501_2,000

..

..

..

50,000,001_100,000,000

This is the expected results using that I neeed

Range=CATX('_',r,r1,r2);

a:._0

b:1_100

c:101_500

d:501_1,000

e:1,001_1,500

f:1,501_2,000

..

..

..

x:50,000,001_100,000,000

[Tom]

You said:

---

@Jcorti wrote:

This is the expected results using that I neeed

Range=CATX('_',r,r1,r2);

a:._0

b:1_100

c:101_500

d:501_1,000

e:1,001_1,500

f:1,501_2,000

..

..

..

x:50,000,001_100,000,000

---

You could generate that with a simple DO loop, but you need to explain the pattern better. The beginning of the pattern looks to be every 500 values.  But the last range does NOT follow that pattern.

data ranges ;
  do i=0 to 100000000 by 500 ;
     if i=0 then base=.;
     else base=i-500+1;
    letter = byte(mod(i/500,25)+rank('a')) ;
    range = catx(':',letter,(catx('_',put(base,comma20.),put(i,comma20.))));
    output;
  end;
  keep range ;
run;

Here are the first 4 and last 4 observations.

283
284  data _null_;
285    set ranges nobs=nobs ;
286    if _n_ <= 4 or _n_ > nobs-4 then put range ;
287    if _n_=4 then put '...';
288  run;

a:._0
b:1_500
c:501_1,000
d:1,001_1,500
...
w:99,998,001_99,998,500
x:99,998,501_99,999,000
y:99,999,001_99,999,500
a:99,999,501_100,000,000
NOTE: There were 200001 observations read from the data set WORK.RANGES.

[Shmuel]

@Jcorti, you changed definition from your first post:

Instead a. - y. your example of desired output is a:. - x:.

Anyhow, it can be done in a single data step but you may need adapt adding values according to letter:

data want;
     retain range_from .;
     length var $25;
     do i=rank('a') to rank('y') by 1;
        if byte(i) = 'a' then do;
           var = 'a:._0'; output;
           range_from = 500;
        end; else
        if byte(i) = 'y' then do;
           var='y:.'||strip(put(range_from,comma12.))||'_1,000,000';
           output;
           leave;
        end; else do;
           var = byte(i)||':.'||strip(put(range_from,comma12.));
           range_from + 500;
           var = catx('_',var,strip(put(range_from,comma12.)));
           output;
        end;
    end;
    drop range_from i;
run;

[Jcorti]

Thanks I really like your solution.

Do you know why after execution of this code It generates numeric values?

data test ;
				do i=rank('a') to rank('y') by 1;
				output;
				end;
		
run;

[Shmuel]

---

@Jcorti wrote:

Thanks I really like your solution.

Do you know why after execution of this code It generates numeric values?

data test ;
				do i=rank('a') to rank('y') by 1;
				output;
				end;
		
run;

---

You probably know that Latin letters, each character is one byte length and is a composition of 8 bits

with numeric value between 0 to 255. In ASCII machines the 'a' chacter is 97 and all other letters are

in consequitive order up to 'z' that is 122.

The function RANK interprets the character byte into its numeric value, while BYTE function makes the reverse.

[Jcorti]

Thanks a lot;

I did some adjustement to your code but finally worked.

data output.&cons (drop=r1 r2 "Range Start"n "Range End"n j letter) ;
retain month_var Range "Current Count"n "Current %"n " Previous Count"n "Previous %"n "% Change"n ;
do j=0 to 2499;
set output.&cons;
r1=put(input("Range Start"n,15.),comma12.);
r2=put("Range End"n,comma15.);
letter = byte(mod(j,25)+rank('a')) ;
Range=CATX(':',letter,(CATX('_',r1,r2)));
output;
end;

this is a sample of the variable generated after execution

a:._0
b:1_100
c:101_500
d:501_1,000
e:1,001_1,500
f:1,501_2,000
g:2,001_2,500
h:2,501_5,000
i:5,001_7,500
j:7,501_10,000
k:10,001_15,000
l:15,001_20,000
m:20,001_25,000
n:25,001_30,000
o:30,001_40,000
p:40,001_50,000
q:50,001_100,000
r:100,001_200,000
s:200,001_300,000
t:300,001_500,000
u:500,001_1,000,000
v:1,000,001_5,000,000
w:5,000,001_10,000,000
x:10,000,001_50,000,000
y:50,000,001_100,000,000
a:._0
b:1_100
c:101_500
d:501_1,000
e:1,001_1,500
f:1,501_2,000
g:2,001_2,500
h:2,501_5,000
i:5,001_7,500
j:7,501_10,000
k:10,001_15,000
l:15,001_20,000
m:20,001_25,000
n:25,001_30,000
o:30,001_40,000
p:40,001_50,000
q:50,001_100,000
r:100,001_200,000
s:200,001_300,000
t:300,001_500,000
u:500,001_1,000,000
v:1,000,001_5,000,000
w:5,000,001_10,000,000
x:10,000,001_50,000,000
y:50,000,001_100,000,000

Thanks a lot again!!!!