topic Copy values to other columns based on two year fields in SAS Programming

Copy values to other columns based on two year fields

SasPerson85 — Tue, 10 Mar 2020 21:21:06 GMT

So I have a table that looks like this:

project_num	department	dty_start	dty_end	amount_contrib_years	year_2015	year_2016	year_2017	year_2018	year_2019	year_2020	year_2021	year_2022
30330431	Sociology	2017	2018	3500
32310125	Sociology	2018	2022	38667.8
30330520	Sociology	2018	2019	3750
32310118	Sociology	2015	2018	19250
30330494	Sociology	2018	2019	2225.5
32310132	Sociology	2019	2020	575
30330508	Sociology	2018	2019	2096

I need the table to look like this:

project_num	department	dty_start	dty_end	amount_contrib_years	year_2015	year_2016	year_2017	year_2018	year_2019	year_2020	year_2021	year_2022
30330431	Sociology	2017	2018	3500			3500	3500
32310125	Sociology	2018	2022	38667.8				38667.8	38667.8	38667.8	38667.8	38667.8
30330520	Sociology	2018	2019	3750				3750	3750
32310118	Sociology	2015	2018	19250	19250	19250	19250	19250
30330494	Sociology	2018	2019	2225.5				2225.5	2225.5
32310132	Sociology	2019	2020	575					575	575
30330508	Sociology	2018	2019	2096				2096	2096

So basically I need to copy the values from "amount_contrib_years" to the appropriate year column. So for example, the top record of the table dty_start = 2017 and dty_end = 2018 so year_2017 and year_2018 both get the value 3500.

Also the year_xxxx fields have been generated using proc tabulate by taking the min(dty_start) and max(dty_end) in macro variables. That way those variables will always have the correct year range listed. Maybe I can do something similar for these values but I am not to sure how.

Thank you for help in advance.

Re: Copy values to other columns based on two year fields

novinosrin — Tue, 10 Mar 2020 21:28:47 GMT

Hi @SasPerson85 Please see if this helps

data have;
infile cards truncover;
input project_num :$15.	department :$15.	dty_start	dty_end	amount_contrib_years	year_2015	year_2016	year_2017	year_2018	year_2019	year_2020	year_2021	year_2022;
cards;
30330431	Sociology	2017	2018	3500	 	 	 	 	 	 	 	 
32310125	Sociology	2018	2022	38667.8	 	 	 	 	 	 	 	 
30330520	Sociology	2018	2019	3750	 	 	 	 	 	 	 	 
32310118	Sociology	2015	2018	19250	 	 	 	 	 	 	 	 
30330494	Sociology	2018	2019	2225.5	 	 	 	 	 	 	 	 
32310132	Sociology	2019	2020	575	 	 	 	 	 	 	 	 
30330508	Sociology	2018	2019	2096	 	 	 	 	 	 	 	 
;

data want;
 set have;
 array y(2015:2022) year_2015-year_2022;
 do _n_=dty_start to dty_end;
   y(_n_)=amount_contrib_years;
 end;
run;

project_num	department	dty_start	dty_end	amount_contrib_years	year_2015	year_2016	year_2017	year_2018	year_2019	year_2020	year_2021	year_2022
30330431	Sociology	2017	2018	3500.0	.	.	3500	3500.0	.	.	.	.
32310125	Sociology	2018	2022	38667.8	.	.	.	38667.8	38667.8	38667.8	38667.8	38667.8
30330520	Sociology	2018	2019	3750.0	.	.	.	3750.0	3750.0	.	.	.
32310118	Sociology	2015	2018	19250.0	19250	19250	19250	19250.0	.	.	.	.
30330494	Sociology	2018	2019	2225.5	.	.	.	2225.5	2225.5	.	.	.
32310132	Sociology	2019	2020	575.0	.	.	.	.	575.0	575.0	.	.
30330508	Sociology	2018	2019	2096.0	.	.	.	2096.0	2096.0	.	.

Re: Copy values to other columns based on two year fields

SasPerson85 — Tue, 10 Mar 2020 21:32:56 GMT

Thank you so much for the quick reply and easy to understand solution! I plugged my macro variables into it that gets the min and max of the years and it worked seamlessly!