topic Re: How we create only specific row(observations)/How do we create row at every NEW first row in the dataset? in SAS Data Management

How we create only specific row(observations)/How do we create row at every NEW first row in the dataset?

HarshadMadhamshettiwar — Thu, 09 Apr 2015 21:19:49 GMT

Hello Experts,

I have a SAS dataset like this

product	rank	date_1	date_2	value_a	value_b
101	1	1-Jan-12	2-Feb-14	12	99
101	2	1-Jan-12	2-Feb-14	22	99
101	3	1-Jan-12	2-Feb-14	44	99
102	1	1-Jan-12	2-Feb-14	45	88
102	2	1-Jan-12	2-Feb-14	85	88
102	3	1-Jan-12	2-Feb-14	56	88
103	1	1-Jan-12	2-Feb-14	78	77
103	2	1-Jan-12	2-Feb-14	46	77

And I want to add a "RANK'" 0(zero) line which will be the first row for every product and for this first row it should have the values of

1) "date_1=date_2" ,

2) "date_2=date_2(unchanged ie. of "Rank" 1)",

3) "value_a=value_b",

4) "value_b=Value_b(Unchanged ie. of "Rank" 1)"

like this:

product	rank	date_1	date_2	value_a	value_b
101	0	2-Feb-14	2-Feb-14	99	99
101	1	1-Jan-12	2-Feb-14	12	99
101	2	1-Jan-12	2-Feb-14	22	99
101	3	1-Jan-12	2-Feb-14	44	99
102	0	2-Feb-14	3-Mar-15	88	88
102	1	1-Jan-12	3-Mar-15	45	88
102	2	1-Jan-12	3-Mar-15	85	88
102	3	1-Jan-12	3-Mar-15	56	88
103	0	2-Feb-14	5-May-14	77	77
103	1	1-Jan-12	5-May-14	78	77
103	2	1-Jan-12	5-May-14	46	77

In-short I want to add a rank zero row with all the values from the row two except for the value_1 and value_b.

How do we do this programatically.

Thanks.

Harshad M. 🙂

Re: How we create only specific row(observations)/How do we create row at every NEW first row in the dataset?

TomKari — Thu, 09 Apr 2015 21:51:20 GMT

Hi, Harshad

I'm wondering if this is a class assignment? Have you tried this? If you have, please post your code for comments.

Tom

Re: How we create only specific row(observations)/How do we create row at every NEW first row in the dataset?

morgalr — Thu, 09 Apr 2015 23:56:37 GMT

Easiest way I know is to manually output.

do while(not eof);

set mylib.inputstuff end=eof;

if first.myVar then do;

*do header stuff here

end;

*whatever processing you want for regular body

output;

end;

note: you need to be ordered by myVar for first to work.

Re: How we create only specific row(observations)/How do we create row at every NEW first row in the dataset?

Kurt_Bremser — Fri, 10 Apr 2015 05:43:54 GMT

Order the dataset by product and rank (unless it is already ordered).

Then

data want;

set have;

by product;

output; * do this first to preserve the values of the rank = 1 observation!;

if first.product

then do;

* assign values to variables as stated;

rank = 0;

output;

end;

run;

/* now, move the rank = 0 up */

proc sort data=want;

by product rank;

run;

Re: How we create only specific row(observations)/How do we create row at every NEW first row in the dataset?

pradeepalankar — Fri, 10 Apr 2015 07:35:30 GMT

data want;

set have;

by product;

if first.product then do;

output;

product=product;

rank=0;

date_1=date_2;

date_2=date_2;

value_a=value_b;

value_b=Value_b;

end;

output;

run;

proc sort data=want;

by product rank;

run;

Re: How we create only specific row(observations)/How do we create row at every NEW first row in the dataset?

HarshadMadhamshettiwar — Fri, 10 Apr 2015 10:01:49 GMT

Hi TomKari,

No it's not any class assignment.

I wrote the code exactly the pradeepalankar have posted except that I did not preserve the first row by outputting it. Hence I was facing the issues.

CODE:

data want;

set have;

by product;

if first.product then do;

product=product;

rank=0;

date_1=date_2;

date_2=date_2;

value_a=value_b;

value_b=Value_b;

output;

end;

run;

Re: How we create only specific row(observations)/How do we create row at every NEW first row in the dataset?

HarshadMadhamshettiwar — Fri, 10 Apr 2015 10:03:44 GMT

Thank you all.

Re: How we create only specific row(observations)/How do we create row at every NEW first row in the dataset?

Ksharp — Fri, 10 Apr 2015 14:17:26 GMT

Try to avoid proc sort.

data have;
input product rank date1 : $20. date2 : $20. value_a value_b;
cards;
101     1     1-Jan-12     2-Feb-14     12     99
101     2     1-Jan-12     2-Feb-14     22     99
101     3     1-Jan-12     2-Feb-14     44     99
102     1     1-Jan-12     2-Feb-14     45     88
102     2     1-Jan-12     2-Feb-14     85     88
102     3     1-Jan-12     2-Feb-14     56     88
103     1     1-Jan-12     2-Feb-14     78     77
103     2     1-Jan-12     2-Feb-14     46     77
;
run;
data want;
 set have;
 by product;
 if first.product then do;
  rank=0; date1=date2;value_a=value_b;output;
  set have point=_n_; output;
 end;
 else output;
run;

Xia Keshan