topic Find last value and create variable in SAS Programming

Find last value and create variable

127 — Thu, 15 Nov 2018 23:10:08 GMT

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

Re: Find last value and create variable

novinosrin — Thu, 15 Nov 2018 23:36:25 GMT


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;

Re: Find last value and create variable

s_lassen — Fri, 16 Nov 2018 11:42:58 GMT

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;

Re: Find last value and create variable

SmithCJGVSU — Fri, 16 Nov 2018 13:26:04 GMT

@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.

Re: Find last value and create variable

SmithCJGVSU — Fri, 16 Nov 2018 13:28:06 GMT

I like this too! Two great solutions.

Re: Find last value and create variable

novinosrin — Fri, 16 Nov 2018 15:05:26 GMT

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

Re: Find last value and create variable

127 — Fri, 16 Nov 2018 15:49:15 GMT

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

Re: Find last value and create variable

wadams — Mon, 16 Aug 2021 18:38:56 GMT

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

Re: Find last value and create variable

novinosrin — Mon, 16 Aug 2021 19:49:39 GMT

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.

Re: Find last value and create variable

wadams — Wed, 08 Sep 2021 21:17:33 GMT

This worked perfectly. Thank you so much @novinosrin!