a proc sql and a case statement

[GN0001]

Hello team,

I came to write a case statement in a proc sql, I get error 22-322 expecting a name.

proc sql;
 create table mytable as
select distict b.*,
case
when b.rate < 35 then 'do this'
when b.rate < 50 then 'do that'
else 'Error'
end as 'this is final rate', a.plan, a.diagcode
from tableb  b left outer join tablea a on b.plan=a.plan;
quit;

Any help?

I get error on this part: 'this is final rate'

Regards,

Blue Blue

[ballardw]

---

@GN0001 wrote:

Hello team,

I came to write a case statement in a proc sql, I get error 22-322 expecting a name.

proc sql;
 create table mytable as
select distict b.*,
case
when b.rate < 35 then 'do this'
when b.rate < 50 then 'do that'
else 'Error'
end as 'this is final rate', a.plan, a.diagcode
from tableb  b left outer join tablea a on b.plan=a.plan;
quit;

Any help?

Regards,

Blue Blue

---

Highlighted text is not a valid SAS variable name. If you insist on using non-standard named variables then the name must be in quotes and followed with an N to indicate that it is a  name literal. "Nonstandard name"n for example.

And you will have to have the SAS system option of Validvarname=any; set as well.

What is lost by using a name like This_is_final_rate? If you want other text to appear then use a LABEL for the variable.

Proc sql;
   create table junk as
   select *,
      case 
         when sex= 'F' then 'something'
         when sex= 'M' then 'something else'
         else 'Error'
      end as newvar label='This is a Sex based value'
   from sashelp.class
   ;
run;

[GN0001]

Hello,

Highlighted text is not a valid SAS variable name. If you insist on using non-standard named variables then the name must be in quotes and followed with an N to indicate that it is a  name literal. "Nonstandard name"n for example.

Where should I add system option of Validvarname=any; ? Wherever, I ues Validvarname, it says: Statement is not valid or it is used out of proper order.

What is lost by using a name like This_is_final_rate? If you want other text to appear then use a LABEL for the variable.

Label is ignored and newVar appears as the header for the variable.

Any other suggestion? What did I do wrong?

Thanks for all,

Blue Blue

[PGStats]

This is a syntax error because "this is final rate" is a string, not a variable name. You can tell SAS to consider that name as a variable name by adding the suffix n

.... end as "this is final rate"n, ...

[GN0001]

Hello,

When I add: "This is final total"n, I have to set  Validvarname=any;

without validvarname, it says too long for a sas name in this context.

with validvarname, error is: statement is not valid or it is used out of proper order.

with newvar label='This is final total, newvar is being show as the header for variable in the dataset.

Regards,

blue blue

[ballardw]

---

@GN0001 wrote:

Hello,

When I add: "This is final total"n, I have to set  Validvarname=any;

without validvarname, it says too long for a sas name in this context.

with validvarname, error is: statement is not valid or it is used out of proper order.

with newvar label='This is final total, newvar is being show as the header for variable in the dataset.

Regards,

blue blue

---

Validvarname is a SAS system option.

You set it with: options validvarname=any;

Where are you using the variable that the LABEL doesn't appear? A few procedures, like proc print, have to be told to use the label. Most procedures will use the label by default or the name and the label.

If you are looking at the data set using the table viewer then you may have the "show variable name" option set instead of show label.

[GN0001]

Hello all,

I use it in proc sql before create table statement.

Regards,

Blue blue

[ballardw]

---

@GN0001 wrote:

Hello all,

I use it in proc sql before create table statement.

Regards,

Blue blue

---

Use what? Not very informative.

[GN0001]

Hello,

I changed the place of this: options validvarname=any;

and I exactly used: options validvarname=any;

before proc sql.

Thanks,

Blue Blue

[Sajid01]

Hello @GN0001 

It would have helped greatly if you had put the log along with the code in the post.
Secondly when you are giving us a code, try to post the code using data sets available in SASHELP.
Carry out a test run before posting.
I do not see in any error in the code when I run using SASHELP.CLASS
I haven't  used "option validvarname=any"

proc sql;
 create table mytable as
select distinct b.*,
case
when b.age*5 < 35 then 'do this'
when b.age*5 < 50 then 'do that'
else 'Error'
end as 'this is final rate'n, a.name as aname, a.age as aage
from sashelp.class b left outer join sashelp.class a on b.name=a.name;
quit;