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.
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;
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
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;
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.
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;
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;
Thank you all.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.