topic a proc sql and a case statement in SAS Programming

a proc sql and a case statement

GN0001 — Wed, 30 Jun 2021 23:17:38 GMT

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

Re: a proc sql and a case statement

ballardw — Wed, 30 Jun 2021 23:22:05 GMT

@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;

Re: a proc sql and a case statement

PGStats — Wed, 30 Jun 2021 23:22:26 GMT

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, ...

Re: a proc sql and a case statement

GN0001 — Thu, 01 Jul 2021 00:15:26 GMT

Hello,

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

Re: a proc sql and a case statement

GN0001 — Thu, 01 Jul 2021 00:20:31 GMT

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

Re: a proc sql and a case statement

ballardw — Thu, 01 Jul 2021 00:36:27 GMT

@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.

Re: a proc sql and a case statement

GN0001 — Thu, 01 Jul 2021 02:56:14 GMT

Hello all,

I use it in proc sql before create table statement.

Regards,

Blue blue

Re: a proc sql and a case statement

ballardw — Thu, 01 Jul 2021 03:16:31 GMT

@GN0001 wrote:

Hello all,

I use it in proc sql before create table statement.

Regards,

Blue blue

Use what? Not very informative.

Re: a proc sql and a case statement

GN0001 — Thu, 01 Jul 2021 04:15:29 GMT

Hello,

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

and I exactly used: options validvarname=any;

before proc sql.

Thanks,

Blue Blue

Re: a proc sql and a case statement

Sajid01 — Thu, 01 Jul 2021 13:45:32 GMT

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;