BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
vaidas
Fluorite | Level 6

Input function is failing to convert in to numeric:

Field :Len=3, $CHAR3. 

Using input function I am getting '.' for characters 1 to 99 and conversion works only for characters 100 to 999.

What could be done  for this large data set analysis? 

 

Proc SQL ;
Create Table Test As
Select distinct
input(A.Field,8.) as Field1,
count(A.ID) as Field2

From &Source_Curr as A

group by A.ID
HAVING Field1 <> Field2

 

Field values:

1
10
11
12
13
14
15
159
16
17
18
19
2
20
228
24
243
25
26
27
29
3
33
37
4
46
5
55
59
6
7
78
8
82
9
?

Above mentioned values are part of the large existing data set that is part of SAS library. 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

Your values LOOK fine (other than the question mark).  Perhaps the data is not what it looks like?  Try printing the data with $HEX6. format attached to that variable.  Does the value '10' show as either '313020' or  '203130'?  If that non digit character is not '20'x (aka space) then you will need to clean the field first. 

For example you could use the compress function and only keep the digits.

input(compress(A.field,,'dk'),8.)

 

View solution in original post

14 REPLIES 14
PaigeMiller
Diamond | Level 26

Please show us your actual code. Please show us a portion of the actual input data.

--
Paige Miller
vaidas
Fluorite | Level 6

Proc SQL ;
Create Table Test As
Select distinct
input(A.Field,8.) as Field1

 

Multiple input versions were used. Neither of them is working.

PaigeMiller
Diamond | Level 26

@vaidas 

You have to show us a portion of your data in variable A

--
Paige Miller
vaidas
Fluorite | Level 6
It is updated.
PaigeMiller
Diamond | Level 26

@vaidas wrote:
It is updated.

I don't see where you provide a portion of your data set

--
Paige Miller
vaidas
Fluorite | Level 6

1
10
11
12
13
14
15
159
16
17
18
19
2
20
228
24
243
25
26
27
29
3
33
37
4
46
5
55
59
6
7
78
8
82
9
?

PaigeMiller
Diamond | Level 26

Works for me.

 

data have; 
input field $;
cards;
1
10
11
12
13
14
15
159
16
17
18
19
2
20
228
24
243
;
proc sql;
	create table want as select input(field,8.) as field1 from have;
quit;

Of course, when you type the data into your message as plain old text, maybe that doesn't give us the exact same result as using your actual SAS data set named &source_curr. Could you please provide that data set following these instructions: How to create a data step version of your data AKA generate sample data for forums

--
Paige Miller
vaidas
Fluorite | Level 6

Thanks. The data already exist in the SAS library I am not inputing these values. Therefore I can't see how it would work in my case.

data have; 
input field $;
cards;

 

PaigeMiller
Diamond | Level 26

I have to create a data set to run code, that's why I have to input the data from the text you typed. But when I do that, I get the expected results and not the results you stated.

 

You have a SAS data set already so you don't have to do that.

 

But my dataset probably doesn't match yours in some important way, which is why I asked you to follow these instructions and provide us the data set: How to create a data step version of your data AKA generate sample data for forums

--
Paige Miller
data_null__
Jade | Level 19

Yes seem to have omitted the important part from your post.

vaidas
Fluorite | Level 6
It was updated.
Krueger
Pyrite | Level 9

Are you seeing any issues with the data you provided? I ran the below and it's converting to numeric without issue?

 

data have;
	input Field1 $3.;
	datalines;
1
10
11
12
13
14
15
159
16
17
18
19
2
20
228
24
243
25
26
27
28
29
3
33
37
4
46
5
55
59
6
7
78
8
82
9
?
;
run;

data want;
	set have;
	test = input(field1,8.);
run;
Tom
Super User Tom
Super User

Your values LOOK fine (other than the question mark).  Perhaps the data is not what it looks like?  Try printing the data with $HEX6. format attached to that variable.  Does the value '10' show as either '313020' or  '203130'?  If that non digit character is not '20'x (aka space) then you will need to clean the field first. 

For example you could use the compress function and only keep the digits.

input(compress(A.field,,'dk'),8.)

 

vaidas
Fluorite | Level 6

Thanks a lot Tom. It works.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 14 replies
  • 1478 views
  • 1 like
  • 5 in conversation