Finding Last Column with Value and Save Column Name or Number

A_SAS_Man — Tue, 15 Oct 2019 19:54:46 GMT

I am trying to loop across the columns of my data set and find the last column of a set of sequentially named columns has data in it and store off some indicator of which column that is. My input data looks as follows

data input;
 input id1 supply1 supply2 supply3 supply4;
 datalines;
1 20 28 .  .
1 30 30 30 30
1 20 28 28 .
2 20 28 .  .
2 30 30 28 28
3 30 .  .  .
4 30 28 30 30
5 20 30 .  .
6 10 28 28 28
6 10 28 30 .
 ;

And I am trying to achieve something along the lines of the following two outputs:

data output;
 input id1 max;
 datalines;
1 2
1 4
1 3
2 2
2 4
3 1
4 4
5 2
6 4
6 3
 ;

data output2;
 input id1 max $10.;
 datalines;
1 supply2
1 supply4
1 supply3
2 supply2
2 supply4
3 supply1
4 supply4
5 supply2
6 supply4
6 supply3
 ;

I am really at a loss as to what is the best way to accomplish something like this. Either of the outputs can be attached as a new column to the original input as well, as that is the end goal is to have a column on each line indicating what the last sequential column with data is for each row. Thanks for any help you can give me

Re: Finding Last Column with Value and Save Column Name or Number

novinosrin — Tue, 15 Oct 2019 20:05:14 GMT


data input;
 input id1 supply1 supply2 supply3 supply4;
 datalines;
1 20 28 .  .
1 30 30 30 30
1 20 28 28 .
2 20 28 .  .
2 30 30 28 28
3 30 .  .  .
4 30 28 30 30
5 20 30 .  .
6 10 28 28 28
6 10 28 30 .
 ;

data want1;
set input;
array s(*)  supply4- supply1;
max=dim(s)-whichn(coalesce(of s(*)),of s(*))+1;
run;

/*OR*/

Better to have both name and position in one solution

data want2;
set input;
array s(*)  supply4- supply1;
array t(*)  supply1- supply4;
max=dim(s)-whichn(coalesce(of s(*)),of s(*))+1;
var_name=vname(t(max));
run;

topic Re: Finding Last Column with Value and Save Column Name or Number in SAS Programming

Finding Last Column with Value and Save Column Name or Number

Re: Finding Last Column with Value and Save Column Name or Number