topic Re: Create a new row using current rows in SAS Programming

Create a new row using current rows

jonatan_velarde — Wed, 02 Mar 2022 11:34:36 GMT

Good day:

I have this data set:

data have;

input Order ID;

cards;

1 ROBERT

2 CARLOS

3 EDWARD

4 JHON

;

now i need this:

ORDER ID STATUS

1 ROBERT ok

1.1 ROBERT ok

1.2 ROBERT ok

1.3 ROBERT ok

2 CARLOS ok

2.1 CARLOS ok

2.2 CARLOS ok

2.3 CARLOS ok

3 EDWARD ok

3.1 EDWARD ok

3.2 EDWARD ok

3.3 EDWARD ok

4 JHON ok

4.1 JHON ok

4.2 JHON ok

4.3 JHON ok

Re: Create a new row using current rows

Ksharp — Wed, 02 Mar 2022 12:44:11 GMT

data have;
input Order ID $;
cards;
1 ROBERT
2 CARLOS
3 EDWARD
4 JHON
;

data want;
 set have;
 status='ok';
output;
 do i=1 to 3;
  order=order+0.1;output;
 end;
 drop i;
run;

Re: Create a new row using current rows

PeterClemmensen — Wed, 02 Mar 2022 12:12:09 GMT

data have;
input Order ID $;
cards;
1 ROBERT 
2 CARLOS 
3 EDWARD 
4 JHON   
;

data want(drop = i o);
   set have(rename = Order = o);
   status = 'ok';
   order = put(o, 8. -l);
   output;
   do i = 1 to 3;
      order = catx('.', cats(o), i);
	  output;
   end;
run;

Re: Create a new row using current rows

Amir — Wed, 02 Mar 2022 12:40:55 GMT

Hi @jonatan_velarde,

How about:

data have;
   input Order ID $8.;
   cards;
1 ROBERT
2 CARLOS
3 EDWARD
4 JHON
;


data want(drop = i);
   set have;

   retain status 'OK';

   do i = 1 to 4;
      output;
      order = order + 0.1;
   end;
run;

Kind regards,

Amir.

Re: Create a new row using current rows

Amir — Wed, 02 Mar 2022 12:48:18 GMT

Hi @jonatan_velarde,

Another alternative:

data have;
   input Order ID $8.;
   cards;
1 ROBERT
2 CARLOS
3 EDWARD
4 JHON
;


data want;
   set have;

   retain status 'OK';

   do order = order to (order + 0.3) by 0.1;
      output;
   end;
run;

Kind regards,

Amir.

Re: Create a new row using current rows

FreelanceReinh — Wed, 02 Mar 2022 14:56:33 GMT

Hello @jonatan_velarde,

I would consider storing the two levels of ORDER (integer and decimal part in your description) in separate variables if possible.

data test;
retain order1 order2 id;
retain status 'ok';
set have(rename=(order=order1));
do order2=., 1 to 3;
  output;
end;
run;

Thus you would avoid these potential issues:

Values like "m.n" (with integers m and n) in a character variable (as suggested by @PeterClemmensen) would require precautions such as inserting leading blanks or zeros for the case that some of the m or n get greater than 9. Otherwise, the default alphabetic sort order might not be satisfactory.
Numeric values m.n are prone to numeric representation issues:
```
607   data _null_;
608   if 2.2+0.1 ne 2.3 then put 'Surprised?';
609   run;

Surprised?
NOTE: DATA statement used (Total process time):
```
So you would need to modify the other code suggestions (e.g., apply the ROUND function) to avoid unexpected results. Plus the issue if n>9.

Re: Create a new row using current rows

ghosh — Wed, 02 Mar 2022 15:31:09 GMT

Another approach, using Cartesian product, that also gives you the flexibility of using letters, example, in the order variable.

data have;
input Order ID $;
cards;
1 ROBERT 
2 CARLOS 
3 EDWARD 
4 JHON   
;
data seq;
  input n;
cards;
0.0
0.1
0.2
0.3
;  
proc sql;
   create table Cart as
   select have.*,seq.*
   from have, seq;
quit;
data want;
   set Cart;
      retain status 'ok';
     Order=order+n;
     drop n;
 run;
proc sort;
  by Order ID;
run;
proc print;
run;