BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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. 🙂

1 ACCEPTED SOLUTION

Accepted Solutions
pradeepalankar
Obsidian | Level 7

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

7 REPLIES 7
TomKari
Onyx | Level 15

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

HarshadMadhamshettiwar
Obsidian | Level 7

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;

morgalr
Obsidian | Level 7

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.

Kurt_Bremser
Super User

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;

pradeepalankar
Obsidian | Level 7

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;

Ksharp
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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