Hi to All
Can somebody help me with this.
This is my table.
data
input
datalines
A 6503 6505
B 302 308
C 10 13
;
run
I need to add new rows according different between nubers two cell in column and fill like in table below:
data
table_2;
inputname $ from_to;
datalines;
A 6503
A 6504
A 6505
B 302
B 303
B 304
B 305
B 306
B 307
B 308
C 10
C 11
C 12
C 13
;
run
;
Thank you in advance.
options nocenter;
data table_1;
input
name $ from to;
datalines
;
A 6503 6505
B 302 308
C 10 13
;
run;
data table_2(keep=name from_to);
set table_1;
do i=from to to;
from_to=i;
output;
end;
run;
proc print;run;
Obs name from_to
1 A 6503
2 A 6504
3 A 6505
4 B 302
5 B 303
6 B 304
7 B 305
8 B 306
9 B 307
10 B 308
11 C 10
12 C 11
13 C 12
14 C 13
Linlin
options nocenter;
data table_1;
input
name $ from to;
datalines
;
A 6503 6505
B 302 308
C 10 13
;
run;
data table_2(keep=name from_to);
set table_1;
do i=from to to;
from_to=i;
output;
end;
run;
proc print;run;
Obs name from_to
1 A 6503
2 A 6504
3 A 6505
4 B 302
5 B 303
6 B 304
7 B 305
8 B 306
9 B 307
10 B 308
11 C 10
12 C 11
13 C 12
14 C 13
Linlin
Thank you very much Linlin.
Working like a charm.
Hi Linlin
Do you have a solution in a case when valuses are formated like text:
data table_1;
input
name $ low $ high $;
datalines
;
A Y010 Y013
B B2R300 B2R305
C NN10 NN13
D 6503 6505
;
run;
Variable values are the only ones behind the last letter. for example correct result will be:
B2R300
B2R301
B2R302
B2R303
B2R304
B2R305
Thank you in advance.
Bob
You could just use a slight variant of Linlin's code:
data table_1;
input name $ low $ high $;
datalines;
A Y010 Y013
B B2R300 B2R305
C NN10 NN13
D 6503 6505
;
run;
data table_2(keep=name from_to);
set table_1;
do i=compress(low,,"kd") to compress(high,,"kd");
if anyalpha(low) then
from_to=catt(substr(low,1,anydigit(low)-1),i);
else from_to=strip(put(i,best12.));
output;
end;
run;
Bob,
By following Art's lead, I hope the following code will meet your need:
data table_1;
input
name $ low $ high $;
datalines
;
A Y010 Y013
B B2R300 B2R305
C NN10 NN13
D 6503 6505
;
run;
data want (keep=name from_to);
set table_1;
_l=put(strip(input(substr(low,ANYALPHA(low,-10)+1),4.)),4.);
l=length(_l);
put l;
_h=substr(high,ANYALPHA(high,-10)+1);
do _n_=_l to _h;
if ANYALPHA(low)=0 then from_to=strip(put(_n_,10.));
else from_to=substr(low,1,length(low)-length(_l))||strip(_n_);
output;
end;
run;
Regards,
Haikuo
Basically, you just need a DO loop and an OUTPUT statement.
data table_2 (KEEP=name from_to);
input
name $ fromnum tonum;
DO from_to=fromnum to tonum;
output;
END;
datalines;
A 6503 6505
B 302 308
C 10 13
;
run;
I tend to shy away from using SAS keywords as variable names ("to" and "from"). SAS is usually good about interpreting names in context, but it can be confusing for me (and when the SAS interpreter makes a mistake, it can have serious consequences).
Doc Muhlbaier
Duke
Thak you very much Doc@Duke
Your solution is correct as well as solutions from Linlin.
You are right about keywords. Next time I'll be carefull.
Bob
Let me try it.
data table_1; input name $ low $ high $; datalines; A Y010 Y013 B B2R300 B2R305 C NN10 NN13 D 6503 6505 ; run; data want; set table_1; length pre_low pre_high want $ 40; _low=input(strip(scan(low,-1, ,'kd')),best8.); l=find(low,strip(_low)); _high=input(strip(scan(high,-1, ,'kd')),best8.); h=find(high,strip(_high)); if l eq 1 then call missing(pre_low); else do;ll=l-1;pre_low=substr(low,1,ll) ; end; if h eq 1 then call missing(pre_high); else do;hh=h-1;pre_high=substr(high,1,hh) ; end; do value=_low to _high; want=cats(pre_low,value); output; end; keep name want; run;Ksharp
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 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.