topic Re: Country and Data data - calculate the difference between today and yesterday within country in SAS Programming

Country and Data data - calculate the difference between today and yesterday within country

andrewjmdata — Mon, 09 Mar 2020 15:10:59 GMT

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

Re: Country and Data data - calculate the difference between today and yesterday within country

Astounding — Mon, 09 Mar 2020 16:56:01 GMT

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;

Re: Country and Data data - calculate the difference between today and yesterday within country

andrewjmdata — Fri, 13 Mar 2020 18:46:20 GMT

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