Hello,
I am working on SAS Code that will allow me to de-range a data set. I need to create a list that has the mapping from var1 to var2. I have done some set up work and I now have a data set that looks like this:
var1: var2 (character):
a 305
a 306-308
b 400
b 401-405
what I need is an output that looks like this
var1: var2:
a 305
a 306
a 307
a 308
b 400
b 401
b 402
b 403
b 404
b 405....
Hopefully this makes sense!
data have;
input var1$ var2 $;
cards;
a 305
a 306-308
b 400
b 401-405
;
data want(rename=(new=var2));
set have;
var3=input(scan(var2,2,'-'),best.);
_var2=input(scan(var2,1,'-'),best.);
if var3 ne . then do i = _var2 to var3;
new=strip(put(i,best.));
output;
end;
drop var2 i;
run;
In the final result, do you want VAR2 to remain character?
Data want;
length var2 $7;
infile datalines;
input var1 $ var2 $;
if index(var2,'-') = 0 then output;
else do;
v1 = scan(var1,1,'-');
v2 = scan(var2,2,'-');
do varx = v1 to v2;
var2 = varx;
output;
end;
drop v1 v2 varx;
datalines;
a 305
a 306-308
b 400
b 401-405
;
run;
data have;
input var1$ var2 $;
cards;
a 305
a 306-308
b 400
b 401-405
;
data want(rename=(new=var2));
set have;
var3=input(scan(var2,2,'-'),best.);
_var2=input(scan(var2,1,'-'),best.);
if var3 ne . then do i = _var2 to var3;
new=strip(put(i,best.));
output;
end;
drop var2 i;
run;
You may need a do loop,
data have;
input var1 :$8. var2:$8.;
datalines;
a 305
a 306-308
b 400
b 401-405
;
run;
data want(drop=i);
set have;
do i=input(scan(var2,1,'-','m'),$3.) to ifn(countw(var2,'-')>1,input(scan(var2,2,'-','m'),$3.),input(scan(var2,1,'-','m'),$3.));
var2=put(i,3.);
output;
end;
run;
Are these always 3 digit numbers?
data have;
input var1$ var2 $;
cards;
a 305
a 306-308
b 400
b 401-405
;
/*if numeric is the required output*/
data want;
set have;
do n=input(scan(var2,1,'-'),8.) to input(scan(var2,-1,'-'),8.);
output;
end;
rename n=var2;
drop var2;
run;
/*if char is the required output*/
data want;
set have;
do _n_=input(scan(var2,1,'-'),8.) to input(scan(var2,-1,'-'),8.);
var2=put(_n_,best8. -l);
output;
end;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.