topic Re: proc format value in proc SQL in SAS Procedures

proc format value in proc SQL

vicky07 — Sun, 18 Mar 2012 02:42:40 GMT

Hi,

Is it possible to use Proc format value in Proc SQL select step?. Below is my code

Proc Sql;

Create table test as

select

employee_Id,

put(salary, $sal_rng.) as Sal_rng

from emp_tbl

;

Quit;

I am getting error messsage as "The format $sal_rng was not found or could not be loaded". The same format works fine if i use it in a datastep.

Thanks,

Re: proc format value in proc SQL

Linlin — Sun, 18 Mar 2012 03:25:58 GMT

/*I don't have problem to run the code below which is similar to your code:*/

proc format;

value $sex

a='male'

b='female';

run;

data have;

input id sex$;

cards;

1 a

2 b

3 b

4 a

;

proc sql;

create table want as

select id,put(sex,$sex.) as gender

from have;

quit;

proc print;run;

Obs id gender

1 1 male

2 2 female

3 3 female

4 4 male

Linlin

Re: proc format value in proc SQL

art297 — Sun, 18 Mar 2012 04:09:07 GMT

Looks to me like salary is a numeric variable and that you are getting the error because you are trying to apply a character format.

Re: proc format value in proc SQL

vicky07 — Sun, 18 Mar 2012 04:16:14 GMT

It worked. Thanks!!

Re: proc format value in proc SQL

art297 — Sun, 18 Mar 2012 16:37:23 GMT

I'm confused! Glad you were able to solve your problem but, unless I've missed something, future readers won't know what the problem or solution was. Linlin showed that your original code would work if salary was a character variable, but you had indicated that the code didn't work.