BookmarkSubscribeRSS Feed
Jcorti
Obsidian | Level 7

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;
8 REPLIES 8
PGStats
Opal | Level 21

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

PG
Shmuel
Garnet | Level 18

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
Obsidian | Level 7

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
Super User Tom
Super User

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
Garnet | Level 18

@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
Obsidian | Level 7

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
Garnet | Level 18

@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
Obsidian | Level 7

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

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 Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1150 views
  • 2 likes
  • 4 in conversation