SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
A_SAS_Man
Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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;

 

View solution in original post

1 REPLY 1
novinosrin
Tourmaline | Level 20

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;

 

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 3343 views
  • 0 likes
  • 2 in conversation