topic Re: reuse formatted variable in New SAS User

reuse formatted variable

sasuser_221 — Fri, 04 Oct 2019 20:16:42 GMT

I'm trying to truncate the variable length to 30 and compare with another variable in the same query.

can you help me with the below 2 questions.

1. How to reuse the formatted variable in the query

2. How to limit the length of variable in the case when statement.

proc sql;
create table data1 as select

NAME length =30 as name_formatted,
case when name_formatted = name_2 then 'true' else 'false'

from table 1 a

;
quit;

Re: reuse formatted variable

PGStats — Sat, 05 Oct 2019 03:40:44 GMT

proc sql;
create table data1 as 
select
	substr(NAME,1,30) as name_formatted length=30,
	case when calculated name_formatted = name_2 then 'true' else 'false' end as test
from table1;
quit;

Re: reuse formatted variable

Tom — Sat, 05 Oct 2019 15:04:59 GMT

You don't have any "formatted" variables, you aren't attaching formats to either of the two variables you are calculating.

Did you mean a CALCULATED variable?

Use the CALCULATED keyword to let the parser know that you are referring to a variable that was derived earlier in the list of columns being selected.

create table data1 as 
  select
    NAME length =30 as name_formatted
  , case when CALCULATED name_formatted = name_2 then 'true'
         else 'false' 
    end as flag
from table1 a
;

Re: reuse formatted variable

PGStats — Sat, 05 Oct 2019 19:15:17 GMT

Unfortunately, @Tom , the SQL interpreter doesn't consider a change of string length as a "calculation". Some function (such as substr) or operator must be involved for a new column name to be considered as CALCULATED :

 71         proc sql;
 72         select
 73         name length=2 as nm,
 74         case when calculated nm = "Ja" then "True" else "False" end as flag
 75         from sashelp.class;
 ERROR: The following columns were not found as CALCULATED references in the immediate query: nm.
 NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
 76         quit;
 NOTE: The SAS System stopped processing this step because of errors.

Re: reuse formatted variable

Tom — Sun, 06 Oct 2019 00:08:29 GMT

That is clearly a BUG. A change in name should be enough to require/allow the use of the CALCULATED keyword.

Given that limitation of PROC SQL you should use the SUBSTR() , or perhaps better the SUBSTRN() function to make sure it thinks you are calculating something.