Help using Base SAS procedures

Add rows according different between two cell in column

Accepted Solution Solved
Reply
Contributor
Posts: 60
Accepted Solution

Add rows according different between two cell in column

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.


Accepted Solutions
Solution
‎02-07-2012 10:48 AM
Super Contributor
Posts: 1,636

Re: Add rows according different between two cell in column

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


All Replies
Solution
‎02-07-2012 10:48 AM
Super Contributor
Posts: 1,636

Re: Add rows according different between two cell in column

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

Contributor
Posts: 60

Add rows according different between two cell in column

Thank you very much Linlin.

Working like a charm.

Contributor
Posts: 60

Re: Add rows according different between two cell in column

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

PROC Star
Posts: 7,492

Re: Add rows according different between two cell in column

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;

Respected Advisor
Posts: 3,156

Re: Add rows according different between two cell in column

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

Contributor
Posts: 60

Re: Add rows according different between two cell in column

Thak you very much Hai.kuo for quick respond.

Your solution is correct.

Bob

Trusted Advisor
Posts: 2,116

Add rows according different between two cell in column

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

Contributor
Posts: 60

Re: Add rows according different between two cell in column

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

Super User
Posts: 10,046

Re: Add rows according different between two cell in column

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 183 views
  • 6 likes
  • 6 in conversation