topic Re: Extending a Dataset in SAS Enterprise Guide

Extending a Dataset

eemrun — Thu, 02 Nov 2017 10:06:25 GMT

Rookie here. I am trying to extend my dataset. It has the following structure

Date	A	B	C
31-Mar-17	0.2	0.1	1.1
30-Jun-17	0.4	0.3	1.3
30-Sep-17	0.6	0.7	1.9

I am trying to extend this dataset for a number of periods. Also the variables once the dates are extended need to be a difference between current and previous value. It should follow the following structure:

Date	A	B	C
31-Mar-17	0.2	0.1	1.1
30-Jun-17	0.4	0.3	1.3
30-Sep-17	0.6	0.7	1.9
31-Dec-17	0.2	0.4	0.6
31-Mar-18	-0.4	-0.3	-1.3
30-Jun-18	-0.6	-0.7	-1.9

Any ideas?

Re: Extending a Dataset

RW9 — Thu, 02 Nov 2017 10:17:21 GMT

Post test data in the form of a datastep!

As such this is only pseudocode:

/* This adds a year to the data into a new dataset */
data have1;
  set have;
  date=intnx('year',date,1);
run;

/* set the two datasets */
data want;
  set have (in=a) have1 (in=b);
  if a then do;
    new_a=a;
    new_b=b;
    new_c=c;
  end;
  else do;
    new_a=lag(a)-lag2(a);
    new_b=lag(b)-lag2(b);
    new_c=lag(c)-lag2(c);
  end;
run;

Re: Extending a Dataset

Ksharp — Thu, 02 Nov 2017 13:36:25 GMT


data have;
infile cards expandtabs;
input Date : date11. A	B	C;
format date date11.;
cards;
31-Mar-17	0.2	0.1	1.1
30-Jun-17	0.4	0.3	1.3
30-Sep-17	0.6	0.7	1.9
;
run;

data want;
 set have end=last;
 laga=lag(a);lagb=lag(b);lagc=lag(c);
 output;
 if last then do;
   do i=1 to 3;
    date=intnx('month',date,1,'e');
	_a=a-laga;_b=b-lagb;_c=c-lagc;
	laga=a;lagb=b;lagc=c;
	a=_a;b=_b;c=_c;
	output;
   end;
 end;
 drop i lag: _: ;
 run;

Re: Extending a Dataset

eemrun — Thu, 02 Nov 2017 23:53:06 GMT

Apologies. I will make sure to put it in a data step next time.

Re: Extending a Dataset

eemrun — Thu, 02 Nov 2017 23:53:26 GMT

Worked perfectly. Thanks!!