BookmarkSubscribeRSS Feed
deleted_user
Not applicable
Hi gyes

The table has numeric field, but when I open the table I see the text values.
In query I cannot say like this:
where MyField = 'ABC', because MyField is numeric.
So, I should make informat. How can I do that in SQL query?
3 REPLIES 3
deleted_user
Not applicable
Not sure this is the right forum for this question, but ...

Use the SAS Input function - it converts from one variable type to another.
Here, in this rather bogus example, I use it in conjunction with the substr() function to convert from string to numeric.

DATA test;
LENGTH s $ 2;
INPUT n s;
CARDS;
1 a2
2 g2
3 h4
4 j3
;;;;

PROC SQL;
SELECT * FROM test WHERE Input(substr(s,2),2.) = 2;
QUIT;

Results:

s n
a2 1
g2 2
Cynthia_sas
SAS Super FREQ
Hi!
I read the post slightly differently. He said that:
The table has numeric field, but when I open the table I see the text values.

To me, this means that there's already some kind of user defined format in play that takes his numeric value and turns it into 'ABC' when he views the table.

So rather than the INPUT function, I thought perhaps the PUT function would be of more help. (Even though this isn't an ODS question, an argument could be made that the issue of understanding formats is one of the key issues in producing reports.)

I took your data example and changed it a bit:
[pre]
proc format;
value nfmt 1 = 'ABC'
2 = 'XYZ';
value $chfmt 'a2' = '1'
'g2' = '1'
'h4' = '9'
'j3' = '9';
run;

DATA test;
infile datalines;
input myfield charvar $;
format myfield nfmt. charvar $chfmt.;
return;
datalines;
1 a2
2 g2
1 h4
2 j3
;
run;

title 'Use Put statement to turn number into character for comparison';
PROC SQL;
SELECT *
FROM test
WHERE put(myfield,nfmt.) = 'ABC';
QUIT;

title 'Use Input statement to turn character into number for comparison';
title2 'First, use PUT to get the right CHAR number and then INPUT to turn character val into number';
title 'Use Input statement to turn character into number for comparison';
title2 'First, use PUT to get the right CHAR number and then INPUT to turn character val into number';
PROC SQL;
SELECT myfield, charvar format=$char2.
FROM test
WHERE input(put(charvar,$chfmt.),1.0) = 1;
QUIT;
[/pre]

If you run the first PROC SQL, then you only select the first and third obs in the data -- based on the MYFIELD numeric var -- and you still see 'ABC' in the output results. To make this form of the query work in his situation, he needs to find out the name of the user-defined format that's being used for his numeric variable. A Proc Contents should help out here.

What if the variable in question was character (like a2 or g2) -- and for some reason you needed to do a numeric comparison?? Your substring method works if what you want is the number from the character variable. The place where INPUT might come in handy is where you needed to do remapping of character values to some number that wasn't in the data value (as shown in my format). In this case, then if you do the remapping in a character format, then you can use both PUT and INPUT to do a numeric comparison. Although the simple
[pre]
WHERE PUT(charvar,$chfmt.) = '1';
[/pre]
would also have worked for the second PROC SQL.

cynthia
deleted_user
Not applicable
Yeah, I debated the Put() vs Input(), and thought a little about a user-defined format, but I saw the reference to the informat and, after deciding to put something down, went for the easier Input().

I was really, really tempted to come back to amend my answer, so I thank you for the elaboration.

I just he's gotten what he needs out this.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 3 replies
  • 5194 views
  • 0 likes
  • 2 in conversation