The SAS Output Delivery System and reporting techniques

Informat in SQL query

Reply
N/A
Posts: 0

Informat in SQL query

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?
N/A
Posts: 0

Re: Informat in SQL query

Posted in reply to deleted_user
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
SAS Super FREQ
Posts: 8,868

Re: Informat in SQL query

Posted in reply to deleted_user
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
N/A
Posts: 0

Re: Informat in SQL query

Posted in reply to Cynthia_sas
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.
Ask a Question
Discussion stats
  • 3 replies
  • 360 views
  • 0 likes
  • 2 in conversation