BookmarkSubscribeRSS Feed
andrewjmdata
Obsidian | Level 7

Hi, I have a large data set that has country and dates within each country and a value col1. I want to calculate the percentage change between each day and previous day of col1. The first date for each country, the value should be 0.

 

At moment my code just does difference (diff) If I can get this to work in my code then % should be easy. At moment can't get difference to work correctly.

 

The code I have looks like this and almost works...

 

proc sort data=have;
by country;
run;

 

data want;
set have;
by country;

 

/* this gives me a number 1..n within each country */
if first.country then seq=1;
else seq+1;

 

/* this gives each country a number effectively... prob no necessary */

if seq=1 then counter+1;

 

/* Here I am hoping that it will give me the difference between current and last record in group */
if not first.country then diff=col1-lag(col1);
else diff=0;
run;

 

Where it fails is when there is a 0 in col1 it seems to get the difference between the current record and where there was a value in the col1 last!

 

Any ideas appreciated!

 

Thanks

 

Andrew

2 REPLIES 2
Astounding
PROC Star

Yes, LAG should not be used on just some of the observations or (as you noticed) it gives the wrong answer.  Here is a simple change:

 

data want;
  set have;
  by country;
  diff = dif(col1);
  if first.country then diff=0;
run;
andrewjmdata
Obsidian | Level 7

The way I solved this was to use proc sql. I added a URN to each record using _N_ in a datastep and then joined on urn=urn-1. 

 

Thanks for the other replies.

 

Regards

 

Andrew

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 291 views
  • 0 likes
  • 2 in conversation