Using Substr function

[desireatem]

I am trying to create the variable year as below from _Name_. Below is the code. Any help, comes out incorrect.

data longMLS2;
set longMLS2 ;
year=input(substr(_NAME_, 4), 3.);
drop _name_;
run;

obs	caseid	_Name_	BMI	Year
1	1	BMI_Y2	.	2
2	1	BMI_Y3	.	3
3	1	BMI_Y4	.	3
4	1	BMI_Y5	.
5	1	BMI_Y6	.
6	1	BMI_Y7	.
7	1	BMI_Y8	.
8	1	BMI_Y9	.
9	1	BMI_Y10	19.3579
10	1	BMI_Y11	23.1639
11	1	BMI_Y12	23.7976
12	1	BMI_Y13	24.7655

[PaigeMiller]

substr(_NAME_, 4) applied to the first row of your data set produces the result _Y2. Do you see what is wrong now?

[desireatem]

Does not work for me.

[Reeza]

Use SCAN() since you have a clear delimiter.

Code = scan(_name_, 2, "_");

[desireatem]

Thank you. It is now Y2, Y3.., how do I drop the Y so that I have 2, 3, ...

[Reeza]

Why not add Y to your SCAN() as a delimiter and you'll get just the number.

You may need to change 2 to 3 but it's worth trying first. 

Use INPUT() to convert it to a number, which will likely be your next question, so you can sort correctly. 

Code = input(scan(_name_, 2, "_Y"), 8.);

---

@desireatem wrote:

Thank you. It is now Y2, Y3.., how do I drop the Y so that I have 2, 3, ...

---

[novinosrin · Accepted solution]

HI @Reeza  You taught me anydigit exactly 19 months ago. 

data have;
input obs	caseid	_Name_	$ BMI;*	Year;
cards;
1	1	BMI_Y2	.	2
2	1	BMI_Y3	.	3
3	1	BMI_Y4	.	3
4	1	BMI_Y5	.	 
5	1	BMI_Y6	.	 
6	1	BMI_Y7	.	 
7	1	BMI_Y8	.	 
8	1	BMI_Y9	.	 
9	1	BMI_Y10	19.3579	 
10	1	BMI_Y11	23.1639	 
11	1	BMI_Y12	23.7976	 
12	1	BMI_Y13	24.7655	 
;

data want;
set have;
year=substr(_name_, anydigit(_Name_));
run;

[desireatem]

Thank you very much for your help!

[Reeza]

Yup, there's many ways to slice this one.
Another viable option is COMPRESS() which will keep only digits.

compress(_name_, , 'kd');

[novinosrin]

Reeza's special in my notes :

1. Proc freq sparse

2. Any...... group of functions with precisions

3. Not to trust anydate informats unless you know for real and sure

4. Remerge in SQL if system is fast enough and convenient

5. Informat statement better than a colon format modifier for reading ease 

and the list goes on to 143 points so far. I am definitely not sharing all. It's my selfish effort to take notes. lol But kudos for offering lol 🙂

[novinosrin]

Or Compress with 'kd' modifier

data have;
input obs	caseid	_Name_	$ BMI;*	Year;
cards;
1	1	BMI_Y2	.	2
2	1	BMI_Y3	.	3
3	1	BMI_Y4	.	3
4	1	BMI_Y5	.	 
5	1	BMI_Y6	.	 
6	1	BMI_Y7	.	 
7	1	BMI_Y8	.	 
8	1	BMI_Y9	.	 
9	1	BMI_Y10	19.3579	 
10	1	BMI_Y11	23.1639	 
11	1	BMI_Y12	23.7976	 
12	1	BMI_Y13	24.7655	 
;

data want;
set have;
year=compress(_name_,,'kd');
run;