topic Re: Add rows according different between two cell in column in SAS Procedures

Add rows according different between two cell in column

bob021 — Tue, 07 Feb 2012 15:09:26 GMT

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.

Re: Add rows according different between two cell in column

Linlin — Tue, 07 Feb 2012 15:48:07 GMT

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

Add rows according different between two cell in column

Doc_Duke — Tue, 07 Feb 2012 15:56:08 GMT

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

Add rows according different between two cell in column

bob021 — Wed, 08 Feb 2012 08:39:20 GMT

Thank you very much Linlin.

Working like a charm.

Re: Add rows according different between two cell in column

bob021 — Wed, 08 Feb 2012 08:42:49 GMT

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

Re: Add rows according different between two cell in column

bob021 — Wed, 08 Feb 2012 14:40:21 GMT

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

Re: Add rows according different between two cell in column

art297 — Wed, 08 Feb 2012 14:56:39 GMT

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;

Re: Add rows according different between two cell in column

Haikuo — Wed, 08 Feb 2012 15:41:11 GMT

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

Re: Add rows according different between two cell in column

bob021 — Thu, 09 Feb 2012 07:50:58 GMT

Thak you very much Hai.kuo for quick respond.

Your solution is correct.

Bob

Re: Add rows according different between two cell in column

Ksharp — Fri, 10 Feb 2012 09:45:03 GMT

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