topic Re: Determine which variable to use based on column values in SAS Programming

Determine which variable to use based on column values

zqkal — Thu, 30 May 2019 19:20:19 GMT

Dear SAS users,

I have a data set similar to the one below. I want to calculate "ratio" based values specified on the 'type' variable without hard coding the type values. since the type could be different month to month.

For example:

for row 1:

ratio = (_1a_val * 100)/360;

row 3:
ratio = (_1b_val * 100)/360;

data have; 
input class $ type $ month _1a_val _1b_val _1c_val;
datalines; 
Plas-1 1a1 0 2.5 3.5 4.5
Plas-1 1a1 1 2.5 3.5 4.5
Plas-1 1b1 0 2.5 3.5 4.5
Plas-1 1b1 1 2.5 3.5 4.5
Plas-1 1c1 0 2.5 3.5 4.5
Plas-1 1c1 1 2.5 3.5 4.5
Plas-1 1c1 2 2.5 3.5 4.5
blod-2 1a1 0 1.5 2.5 3.5
blod-2 1a1 1 1.5 2.5 3.5
blod-2 1b1 0 1.5 2.5 3.5
blod-2 1b1 1 1.5 2.5 3.5
blod-2 1b1 2 1.5 2.5 3.5
blod-2 1c1 0 1.5 2.5 3.5
blod-2 1c1 1 1.5 2.5 3.5
blod-2 1c1 2 1.5 2.5 3.5
;
run;

Here is the result. I want

class	type	month	_1a_val	_1b_val	_1c_val	ratio
Plas-1	1a1	0	2.5	3.5	4.5	0.6944444444
Plas-1	1a1	1	2.5	3.5	4.5	0.6944444444
Plas-1	1b1	0	2.5	3.5	4.5	0.9722222222
Plas-1	1b1	1	2.5	3.5	4.5	0.9722222222
Plas-1	1c1	0	2.5	3.5	4.5	1.25
Plas-1	1c1	1	2.5	3.5	4.5	1.25
Plas-1	1c1	2	2.5	3.5	4.5	1.25
blod-2	1a1	0	1.5	2.5	3.5	0.4166666667
blod-2	1a1	1	1.5	2.5	3.5	0.4166666667
blod-2	1b1	0	1.5	2.5	3.5	0.6944444444
blod-2	1b1	1	1.5	2.5	3.5	0.6944444444
blod-2	1b1	2	1.5	2.5	3.5	0.6944444444
blod-2	1c1	0	1.5	2.5	3.5	0.9722222222
blod-2	1c1	1	1.5	2.5	3.5	0.9722222222
blod-2	1c1	2	1.5	2.5	3.5	0.9722222222

Re: Determine which variable to use based on column values

Tom — Thu, 30 May 2019 19:28:42 GMT

Are you saying that the names of the variables can change over time?

If not then just code an IF statement.

if type='1a1' then ratio = (_1a_val * 100)/360;
else if type='1b1' then ratio = (_1b_val * 100)/360;
else if type='1c1' then ratio = (_1c_val * 100)/360;

If you can change how TYPE is coded then it would be even easier.

If you make TYPE an integer from 1 to 3 then you could just use an array.

array value _1a_val _1b_val _1c_val ;
ratio = (value[type] * 100)/360;

Re: Determine which variable to use based on column values

r_behata — Thu, 30 May 2019 20:58:30 GMT

data have; 
input class $ type $ month _1a_val _1b_val _1c_val;
datalines; 
Plas-1 1a1 0 2.5 3.5 4.5
Plas-1 1a1 1 2.5 3.5 4.5
Plas-1 1b1 0 2.5 3.5 4.5
Plas-1 1b1 1 2.5 3.5 4.5
Plas-1 1c1 0 2.5 3.5 4.5
Plas-1 1c1 1 2.5 3.5 4.5
Plas-1 1c1 2 2.5 3.5 4.5
blod-2 1a1 0 1.5 2.5 3.5
blod-2 1a1 1 1.5 2.5 3.5
blod-2 1b1 0 1.5 2.5 3.5
blod-2 1b1 1 1.5 2.5 3.5
blod-2 1b1 2 1.5 2.5 3.5
blod-2 1c1 0 1.5 2.5 3.5
blod-2 1c1 1 1.5 2.5 3.5
blod-2 1c1 2 1.5 2.5 3.5
;
run;

data want;
	array ratio_arr{*} _1a_val _1b_val _1c_val;
	i+1;
	do until(last.type);
		set have;
		by class type;

		if first.class then i=1;
	end;

	do until(last.type);
		set have;
		by class type;

		ratio =(ratio_arr[i]*100)/360;
		output;
	end;

	drop i;
run;

Re: Determine which variable to use based on column values

zqkal — Thu, 30 May 2019 21:23:36 GMT

Thanks for your help. this solution works.