Macro / Looping

[KevinRetric]

Hi all - thank you for this forum. I found a lot of useful answers here while starting to learn SAS. 

I hope you can help me with my little problem here. 

An excerpt from my dataset looks like this: 

ID	set_2016	set_2017	set_2018	set_2019
1	10	20	5	10
2	8	0	10	8
3	5	40	0	9

What I would like to do is to create a new value for each row per column (e.g. New_set_2016 = set_2016 + 10)

Since I have numerous columns, I would like to create a macro or a loop which applies the aforementioned formula to each column in my dataset. 

I am reading through several forums to at least try and build a code but have failed to do so - I hope you could help me out with this. 

Again thank you for this opportunity

Best

Kevin

[sbxkoenk · Accepted solution]

Hello @KevinRetric ,

No need for a macro.

You need an array (or better : 2 arrays) and loop over the array elements.

data work.have;
input ID $ set_2016 set_2017 set_2018 set_2019;
cards;
1	10	20   5	10
2	 8	 0	10	 8
3	 5	40	0	 9
;
run;

data work.want(drop=i set_:);
 set work.have;
 array have{*}     set_2016 -     set_2019;
 array want{*} new_set_2016 - new_set_2019;
 do i=1 to dim(have);
  want(i) = have(i) + 10;
 end;
run;
/* end of program */

Koen

[KevinRetric]

Dear @sbxkoenk  ,

thank you very much for your quick response - your solution worked perfectly. Sorry to bother you with a follow-up...

I found a very interesting financial dataset in the internet, which I would like to use as a learning base - this seems a bit more complicated - I am trying to apply your code, but I  assume this one requires a slightly different approach :)? 

In the following dataset, I would like to apply a formula, which calculates  "(set_2016+track_2016)/FY_31.12.2016" for each year and creates a new set that ranges from "New_2016 - New_2018". 

ID	set_FY2016	set_FY2017	set_FY2018	track_FY2016	track_FY2017	track_FY2018	FY_31.12.2016	FY_31.12.2017	FY_31.12.2018
1	10	20	5	12	212	3	120	204	53
2	8	0	10	4	3	32	82	3	103
3	5	40	0	55	44	3	523	440	12

Given your great example I tried: 

data want (drop=i :_FY:);
set have;
array have{*} Set_FY2016 - FY_31.12.2018;
array want{*} New_Value_FY2016 - New_Value_FY2018;
do i=1 to dim(have);
Want(i) = have(i)+have(i); /*this is where I am stuck, as this takes the whole dataset into consideration, but I would like to apply the respective formula*/
end;
run;

Is this doable with the provided code?

Thank you so much for your input. I wish you a nice day.

Kevin

[maguiremq]

See if this gets you the answer to your latest question. You just have to modify the code a little.

data have;
infile datalines delimiter = ",";
input ID $	set_FY2016	set_FY2017	set_FY2018	track_FY2016	track_FY2017	track_FY2018	FY_31_12_2016	FY_31_12_2017 	FY_31_12_2018;
cards;
1,10,20,5,12,212,3,120,204,53
2,8,0,10,4,3,32,82,3,103
3,5,40,0,55,44,3,523,440,12
;
run;

/* set_2016+track_2016)/FY_31.12.2016 */

data want (drop = i);
	set have;
	array 	_set 	[*] set_:;
	array 	_track 	[*] track_:;
	array 	_fy 	[*] fy_:;
	array 	_new 	[*] new_2016 - new_2018;
	do i = 1 to dim(_set);
		_new[i] = ((_set[i] + _track[i]) / _fy[i]);
	end;
run;
		

Also, please mark @sbxkoenk's solution as the correct one since he answered your original question.

[KevinRetric]

Hello @maguiremq  - thank you so much! I was able to adjust the code and works just perfect. 

I will revise the code and make sure to remember it. I hope it fine to come back with further questions, if I get stuck at some point.

I wish you a very nice day. 

Kevin