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;

 

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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
  • 3842 views
  • 0 likes
  • 2 in conversation