BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
SAS-questioner
Obsidian | Level 7

I have a variable that is all numbers but in character string, because I extracted the numbers from another variable. For example, the variable 'a' is like:

a
1,2
2,3,4

And I created a new variable 'b' by extracting numbers from 'a', like below:

b
1
2
2
3
4

But the new 'b' variable is character string, I tried to convert it to numeric by using:

b_n=input(b,8.);

However, the b_n is all missing values (bunch of '.'s). What's going on with this situation? Could anyone help me? Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
data long;
	set one;
do i =1 to countw(a, ',');
	b=scan(a,i, ',');
        b_n=input(trim(b), 8.);
	output;
end;

run;

Order of operations issue. You are outputting the data before b_n is calculated and only calculating it for the last value.

View solution in original post

10 REPLIES 10
Reeza
Super User

Show your code?

 

EDIT: add a trim to remove any additional white space?

b_n=input(trim(b), 8.);

 

SAS-questioner
Obsidian | Level 7

I tried, still not working, I used the code like this:

data long;
	set one;
do i =1 to countw(a, ',');
	b=scan(a,i, ',');
	output;
end;
b_n=input(trim(b), 8.);
run;

This is the second time that I encountered this. Last time I remember that I extracted data from excel, and it always produced missing values, so I exported to excel and changed the format and imported back.

 

Reeza
Super User
data long;
	set one;
do i =1 to countw(a, ',');
	b=scan(a,i, ',');
        b_n=input(trim(b), 8.);
	output;
end;

run;

Order of operations issue. You are outputting the data before b_n is calculated and only calculating it for the last value.

ballardw
Super User

You really need to show the code of how you get from A to B.

If the purpose is get the numeric value from A into a new variable then don't bother creating B.

 

data have;
   input a $;
datalines;
1,2
2,3,4
;

data want;
   set have;
   do i=1 to countw(a,',');
      bn=input(scan(a,i,','),8.);
      output;
   end;
   drop i;
run;
SAS-questioner
Obsidian | Level 7
Yeah, it works, that's weird that the extra step produced missing values.
Reeza
Super User
It's because your output STATEMENT is before the b_n is calculated in the code.
SAS-questioner
Obsidian | Level 7
Yeah that explains, I tried to put it into different data step and now it works this time.
Tom
Super User Tom
Super User

Why are you telling INPUT() to read only the first 8 bytes of B?  Are you sure B isn't already a number?  If it is then SAS will first convert to a string using BEST12. format and if you only read the first 8 bytes they will all be blanks.  But if it is already a number then there is no need for the INPUT() function call.

 

If it is a string then again why are you only trying to read the first 8 bytes?  The normal numeric function can ready 32 bytes.  And INPUT() does not care if the string is shorter than the width on the informat.  But you might want to remove any leading spaces.

b_n=input(left(b),32.);
SAS-questioner
Obsidian | Level 7
Yeah, I am sure the B isn't already a number. I used the proc contents to check. I changed the 8. to 32., and put left in there, it still didn't work.
mkeintz
PROC Star

@SAS-questioner wrote:
... stuff deleted ...
it still didn't work.

Simply telling us that something "doesn't work" ... doesn't work.

 

Show us the code and the log, and describe what you got vs what you expected.  Help us help you.

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

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
  • 10 replies
  • 2573 views
  • 3 likes
  • 5 in conversation