topic Re: Concatenate with CATX in SAS Programming

Concatenate with CATX

Jcorti — Sun, 26 Feb 2017 04:09:24 GMT

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;

Re: Concatenate with CATX

PGStats — Sun, 26 Feb 2017 05:02:14 GMT

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

Re: Concatenate with CATX

Shmuel — Sun, 26 Feb 2017 05:05:45 GMT

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 ?

Re: Concatenate with CATX

Jcorti — Sun, 26 Feb 2017 15:15:57 GMT

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

Re: Concatenate with CATX

Tom — Sun, 26 Feb 2017 15:50:51 GMT

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.

Re: Concatenate with CATX

Shmuel — Sun, 26 Feb 2017 19:53:39 GMT

@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;

Re: Concatenate with CATX

Jcorti — Mon, 27 Feb 2017 00:54:55 GMT

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

Re: Concatenate with CATX

Jcorti — Mon, 27 Feb 2017 01:03:16 GMT

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;

Re: Concatenate with CATX

Shmuel — Mon, 27 Feb 2017 05:33:58 GMT

@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.