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 |
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;
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;
@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.
works beautifully, thank you and others for this contribution!!!
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
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.
This worked perfectly. Thank you so much @novinosrin!
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.