SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
Contributor
Posts: 37
Accepted Solution

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

Hello Experts,

I have a SAS dataset like this

productrankdate_1date_2value_avalue_b
10111-Jan-122-Feb-141299
10121-Jan-122-Feb-142299
10131-Jan-122-Feb-144499
10211-Jan-122-Feb-144588
10221-Jan-122-Feb-148588
10231-Jan-122-Feb-145688
10311-Jan-122-Feb-147877
10321-Jan-122-Feb-144677

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:

productrankdate_1date_2value_avalue_b
10102-Feb-142-Feb-149999
10111-Jan-122-Feb-141299
10121-Jan-122-Feb-142299
10131-Jan-122-Feb-144499
10202-Feb-143-Mar-158888
10211-Jan-123-Mar-154588
10221-Jan-123-Mar-158588
10231-Jan-123-Mar-155688
10302-Feb-145-May-147777
10311-Jan-125-May-147877
10321-Jan-125-May-144677

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. :-)


Accepted Solutions
Solution
‎04-10-2015 03:35 AM
Frequent Contributor
Posts: 106

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

Posted in reply to HarshadMadhamshettiwar

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;

View solution in original post


All Replies
PROC Star
Posts: 1,167

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

Posted in reply to HarshadMadhamshettiwar

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

Contributor
Posts: 37

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

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;

Contributor
Posts: 27

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

Posted in reply to HarshadMadhamshettiwar

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.

Super User
Posts: 7,768

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

Posted in reply to HarshadMadhamshettiwar

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;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Solution
‎04-10-2015 03:35 AM
Frequent Contributor
Posts: 106

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

Posted in reply to HarshadMadhamshettiwar

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;

Contributor
Posts: 37

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

Posted in reply to pradeepalankar

Thank you all.

Super User
Posts: 10,023

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

Posted in reply to HarshadMadhamshettiwar

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

🔒 This topic is solved and locked.

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

Discussion stats
  • 7 replies
  • 539 views
  • 9 likes
  • 6 in conversation