BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bob021
Calcite | Level 5

Hi to All

Can somebody help me with this.

This is my table.

data

table_1;

input

name $ from to;

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;

input

name $ 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.

1 ACCEPTED SOLUTION

Accepted Solutions
Linlin
Lapis Lazuli | Level 10

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

View solution in original post

9 REPLIES 9
Linlin
Lapis Lazuli | Level 10

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

bob021
Calcite | Level 5

Thank you very much Linlin.

Working like a charm.

bob021
Calcite | Level 5

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

art297
Opal | Level 21

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;

Haikuo
Onyx | Level 15

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

bob021
Calcite | Level 5

Thak you very much Hai.kuo for quick respond.

Your solution is correct.

Bob

Doc_Duke
Rhodochrosite | Level 12

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

bob021
Calcite | Level 5

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

Ksharp
Super User

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

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!

What is Bayesian Analysis?

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.

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
  • 9 replies
  • 882 views
  • 6 likes
  • 6 in conversation