Find last value and create variable

[127]

Any help would be greatly appreciated it. I am trying to create a variable let's say called "NewVar" that would indicate the "time at which someone" quit even though it's not a date. For example I have data that looks like this and the last column shows what the new variable would be: 

Obs	Value_S1	Value_S2	Value_S3	Value_S4	Value_S5	Value_S6	Value_S7	Value_S8	Value_S9	Value_S10	Value_S11	Value_S12	Value_S13	Value_S14	Value_S15	Value_S16	NewVar
1	188	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	1
2	167	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	1
3	233	236	.	.	.	.	.	.	.	.	.	.	.	.	.	.	2
4	278	280	275	275	276	.	.	.	.	.	.	.	.	.	.	.	5
5	253	252			346		248	.	.	.	.	.	.	235	234	234	16
6	213	215	215	218	217	212	212	.	.	.	.	.	.	.	.	.	7
7	281		279	281	281		280	.	.	.	.	.	.	.	.	279	16

[novinosrin · Accepted solution]

data have;
infile cards truncover;
input Value_S1	Value_S2	Value_S3	Value_S4	Value_S5	Value_S6	Value_S7	Value_S8	Value_S9	Value_S10	Value_S11	Value_S12	Value_S13	Value_S14	Value_S15	Value_S16;
cards;
188	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.
167	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.
233	236	.	.	.	.	.	.	.	.	.	.	.	.	.	.
278	280	275	275	276	.	.	.	.	.	.	.	.	.	.	.
253	252	. .		346	.	248	.	.	.	.	.	.	235	234	234
213	215	215	218	217	212	212	.	.	.	.	.	.	.	.	.
281	.	279	281	281	.	280	.	.	.	.	.	.	.	.	279
;

data want;
set have;
array j(*)Value_S16-Value_S1;
_iorc_=coalesce(of j(*));
new_var=dim(j)-whichn(_iorc_,of j(*))+1;
run;

[SmithCJGVSU]

@novinosrin, nice use of WHICHN, plus I didn't realize you can use an array reference as an "OF LIST". You win the day my friend.

[127]

works beautifully, thank you and others for this contribution!!!

[wadams]

Hi @novinosrin - How would the syntax change if the user wants to retain the value of the cell rather than the name of the column.  For example:

Subject 1 = 188

Subject 2 = 167

Subject 3 = 236

Subject 4 = 276

etc.

Thank you!

Bill

[novinosrin]

Hi @wadams  Bill, Sorry in the middle of a meeting at work and so a quick one assuming I understood your requirement-

data want;
  set have;
  array j(*)Value_S16-Value_S1;
  array k(*) Value_S1-Value_S16;*Compile another array Bill's additional request;
  _iorc_=coalesce(of j(*));
  new_var=dim(j)-whichn(_iorc_,of j(*))+1;
  new_value=k(new_var);*Compute another assignment for  Bill's additional request;
run;

Please see if the above suffices. If not, i will take a look in the evening EDT.

[wadams]

This worked perfectly.  Thank you so much @novinosrin!

[s_lassen]

While the solution suggested by @novinosrin is ingenious (and it works), I would prefer something a bit more obvious:

data want;
  set have;
  array values Value_S1-Value_S16;
  do NewVar=dim(values) to 1 by -1 while(missing(values(NewVar)));
    end;
run;

[SmithCJGVSU]

I like this too! Two great solutions.

[novinosrin]

Thank you @SmithCJGVSU and @s_lassen  for the very kind words. Feels very encouraging to pursue harder and further. Pleasant as it was to see the first thing in the morning waking up. 🙂

Hi  @tugtrog   As you know I share pretty stuff ,here is another one. @Cruise , Since, your attitude is great as that of @tugfrog  in honing further, I am pulling you in