I've got a data table which consists of sequental returns from various sources. I want to add a label to the table that identifies where each row sits in the sequence of returns from it's source. For example, I want to label the latest return for each source as "1", the one before that as "2", etc.
I'm using Enterprise Guide, so something that works in the Query Builder would be great.
I'm running a test on the data that compares the latest figure with the previous figures, so ned to be able to know where in the sequence each sits.
Thanks
Could you supply some sample data in the form of a data step with a cards statement so we can give you a better solution?
Hello Chris,
I don't have data to hand, but it's along these lines:
Old Fruit:
Apples 2016 £1.50
Apples 2015 £3
Pears 2015 £1.30
Pears 2014 £1.20
New Fruit:
Apples 2017 £1.10
Pears 2016 £1.90
New fruit is the data that's arrived since the process flow was last run and I have this separated out by creating a static copy of the data as it was last time the process was run and then comparing the two. I want to be able to highlight fruit where the price has doubled since the last data, or where it's fallen since two data points ago for example. I can do all of that, except the bit that labels each row as follows:
Old Fruit:
Apples 2016 £1.50 1
Apples 2015 £3 2
Pears 2015 £1.30 1
Pears 2014 £1.20 2
New Fruit:
Apples 2017 £1.10
Pears 2016 £1.90
In the simplified version of the data above you could do something that counts back from the Year in increments, but this doesn't work for the real data as the gaps in dates aren't consistent, what I need is to be able to compare all of the results for Apples and label them 1 to x in date order.
Does that make more sense?
Thanks
Paul.
I think something like this might help - in a code node try
data have;
infile datalines dlm=",";
length fruit $6;
input fruit $ year cost;
datalines;
Apples,2016,1.5
Apples,2015,3
Pears,2015,1.3
Pears,2014,1.2
Pears,2000,1.1
;
quit;
proc sort data=have out=have_sorted;
by fruit descending year;
quit;
data want;
set have_sorted;
by fruit;
year_rank=(lag(year)-year)+1;
if first.fruit then year_rank=1;
run;
Note that in the data step you need to calculate the year_rank first and then reset it if it's the first observation in the By group - you shouldn't use lag inside a conditional statement or you get incorrect results
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 16. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: