Proc Format cntlin numeric informat

[sc5]

Hi,

I have 2 datasets as below: 'insects' and 'test1'.

Data test1 has thousands of rows with many columns including binary 1/0 ins_ant, ins_bee. ins_beetle.

I want to calculate new columns 'total_weight' and 'total_id' based on the values in Data insects (sum of the corresponding "weight"s and "id"s.)

I'm using a proc format to do this, my code is below. 

I used type = 'I' because my input is a string (insect name) and outputs are numbers, and that's the informat type to use to the documentation here - SAS Help Center: Results: PROC FORMAT

But my final output dataset test2 has null values for the total_weight and total_id columns. 

What am I doing wrong? 

Or please let me know if there's a simpler way to do this (without using proc format, but also without hardcoding anything like names of insects/weights/ids/number of insects etc.) 

Thank you!!

code:

data insects;
length insect $100.;
	insect='ant';
	weight = 10;
	id0 = 1;
	output;

	insect='beetle';
	weight = 20;
	id0 = 2;
	output;

	insect='bee';
	weight = 30;
	id0 = 3;
	output;
run;

data fmt;
	set insects;
	length start $100;
	start = upcase(strip(insect));

	retain type 'I';

	fmtname = 'weight';
	label = weight;
	output fmt;

	fmtname = 'id';
	label = id0;
	output fmt;

	keep start fmtname label type;
run;

proc format cntlin=fmt;
run;

data test1;
	
	patient = 4772;
	ins_ant = 1;
	ins_bee = 1;
	ins_beetle = 0;
	output;

	patient = 6792;
	ins_ant = 0;
	ins_bee = 1;
	ins_beetle = 1;
	output;
run;

data test2;
	set test1;
	total_weight = 0;
	total_id0 = 0;

	array ins_all {*} ins_: ; 
	
	do i_local = 1 to dim(ins_all);
		total_weight = total_weight + ( ins_all[i_local] * input(upcase(strip(scan(vname(ins_all[i_local]),2,'_'))),weight.) );
		total_id0 = total_id0 + ( ins_all[i_local] * input(upcase(strip(scan(vname(ins_all[i_local]),2,'_'))),id.) );
	end;

	drop i_local;
run;

[Astounding]

You may need an iterative solution: fix the earliest problem, then see what problems remain.
Since you report no errors, we won't try to fix any errors. Instead, sort the data set fmt by FMTNAME, before feeding it in to PROC FORMAT.
Last I checked, a new value for FMTNAME triggers replacement of that format, not the addition to previously defined values.

[sc5]

Sorting worked, thank you! 🙂