Desktop productivity for business analysts and programmers

Extending a Dataset

Accepted Solution Solved
Reply
Contributor
Posts: 37
Accepted Solution

Extending a Dataset

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

 

DateABC
31-Mar-170.20.11.1
30-Jun-170.40.31.3
30-Sep-170.60.71.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:

 

DateABC
31-Mar-170.20.11.1
30-Jun-170.40.31.3
30-Sep-170.60.71.9
31-Dec-170.20.40.6
31-Mar-18-0.4-0.3-1.3
30-Jun-18-0.6-0.7-1.9

 

 

Any ideas?


Accepted Solutions
Solution
‎11-02-2017 07:53 PM
Super User
Posts: 10,850

Re: Extending a Dataset


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;

View solution in original post


All Replies
Super User
Super User
Posts: 9,857

Re: Extending a Dataset

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;

Contributor
Posts: 37

Re: Extending a Dataset

Apologies. I will make sure to put it in a data step next time.
Solution
‎11-02-2017 07:53 PM
Super User
Posts: 10,850

Re: Extending a Dataset


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;

Contributor
Posts: 37

Re: Extending a Dataset

Worked perfectly. Thanks!!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 245 views
  • 1 like
  • 3 in conversation