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
;
Or
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
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;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.