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?

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,088

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

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: 26

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

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: 6,928

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

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?

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?

Thank you all.

Super User
Posts: 9,662

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

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.

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

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