struggling with formats used in fedsql

[acordes]

I do not succeed in defining a numeric format in a "case when" construct in fedsql. 

It gives en error in the proc summary code:

ERROR: Variable RENEWED in list does not match type prescribed for this list.

proc cas;
source MPG_toyota;
create table mkt.testa {options  replication=0 REPLACE=true } as select 
a.*, case when b.numbasti='' then put(0, 3.) else put(1, 3.) end as renewed , b.CODOPERA_FOR, b.NUMBASTI_FOR   
from public.denom a left join public.nom b
on a.numbasti=b.numbasti;;
endsource;
fedSQL.execDirect / query=MPG_toyota;
quit;


proc summary data=mkt.testa print ;
format _FECULVTO year. ;
class from_brand _FECULVTO ;
var renewed;
output out=test sum= mean= / autoname;
run;

[tarheel13]

proc summary requires a numeric variable. open up mtk.testa dataset and verify whether renewed is char or numeric. I believe the error is how you're creating renewed in proc cas.

[ballardw]

---

@tarheel13 wrote:

proc summary requires a numeric variable. open up mtk.testa dataset and verify whether renewed is char or numeric. I believe the error is how you're creating renewed in proc cas.

---

For VAR variables. Class, By and ID variables can be character or numeric.

So the variable Renewed in this code is not numeric.

[acordes]

I haven't expressed myself correctly. 

I know that it's not numeric therefore giving an error. 

But HOW do I create a valid numeric variable in fedsql so that it executes correctly?

If I do the same in proc sql then it runs without problems. 

This works:

options casdatalimit=all;
proc sql;
create table testa  as select 
a.*, case when b.numbasti='' then 0 else 1 end as renewed , b.CODOPERA_FOR, b.NUMBASTI_FOR   
from public.denom a left join public.nom b
on a.numbasti=b.numbasti;
quit;

proc summary data=testa print ;
format _FECULVTO year. ;
class from_brand _FECULVTO ;
var renewed;
output out=test sum= mean= / autoname;
run;

[Kurt_Bremser]

Omit the PUT function, which always results in character:

create table mkt.testa {options  replication=0 REPLACE=true } as
  select 
    a.*,
    case when b.numbasti=''
      then 0
      else 1
    end as renewed,
    b.CODOPERA_FOR,
    b.NUMBASTI_FOR   
  from public.denom a left join public.nom b
  on a.numbasti=b.numbasti
;

[acordes]

I had tried before, it still gives error. 

1 %studio_hide_wrapper;
82 proc cas;
83 source MPG_toyota;
84 create table mkt.testa {options replication=0 REPLACE=true } as
85 select
86 a.*,
87 case when b.numbasti=''
88 then 0
89 else 1
90 end as renewed,
91 b.CODOPERA_FOR,
92 b.NUMBASTI_FOR
93 from public.denom a left join public.nom b
94 on a.numbasti=b.numbasti
95 ;
96 endsource;
97 fedSQL.execDirect / query=MPG_toyota;
98 quit;
NOTE: Active Session now MYSESSION.
NOTE: CASDAL driver. Creation of a DATE column has been requested, but is not supported by the CASDAL driver. A DOUBLE PRECISION
column will be created instead. A DATE format will be associated with the column.
NOTE: CASDAL driver. Creation of a DATE column has been requested, but is not supported by the CASDAL driver. A DOUBLE PRECISION
column will be created instead. A DATE format will be associated with the column.
NOTE: CASDAL driver. Creation of a DATE column has been requested, but is not supported by the CASDAL driver. A DOUBLE PRECISION
column will be created instead. A DATE format will be associated with the column.
NOTE: CASDAL driver. Creation of a DATE column has been requested, but is not supported by the CASDAL driver. A DOUBLE PRECISION
column will be created instead. A DATE format will be associated with the column.
NOTE: CASDAL driver. Creation of a DATE column has been requested, but is not supported by the CASDAL driver. A DOUBLE PRECISION
column will be created instead. A DATE format will be associated with the column.
NOTE: CASDAL driver. Creation of a DATE column has been requested, but is not supported by the CASDAL driver. A DOUBLE PRECISION
column will be created instead. A DATE format will be associated with the column.
NOTE: CASDAL driver. Creation of a DATE column has been requested, but is not supported by the CASDAL driver. A DOUBLE PRECISION
column will be created instead. A DATE format will be associated with the column.
NOTE: CASDAL driver. Creation of a TIMESTAMP column has been requested, but is not supported by the CASDAL driver. A DOUBLE
PRECISION column will be created instead. A DATETIME format will be associated with the column.
NOTE: CASDAL driver. Creation of a DATE column has been requested, but is not supported by the CASDAL driver. A DOUBLE PRECISION
column will be created instead. A DATE format will be associated with the column.
NOTE: CASDAL driver. Creation of a DATE column has been requested, but is not supported by the CASDAL driver. A DOUBLE PRECISION
column will be created instead. A DATE format will be associated with the column.
NOTE: CASDAL driver. Creation of a DATE column has been requested, but is not supported by the CASDAL driver. A DOUBLE PRECISION
column will be created instead. A DATE format will be associated with the column.
NOTE: CASDAL driver. Creation of a TIMESTAMP column has been requested, but is not supported by the CASDAL driver. A DOUBLE
PRECISION column will be created instead. A DATETIME format will be associated with the column.
NOTE: CASDAL driver. Creation of a DATE column has been requested, but is not supported by the CASDAL driver. A DOUBLE PRECISION
column will be created instead. A DATE format will be associated with the column.
NOTE: CASDAL driver. Creation of a DATE column has been requested, but is not supported by the CASDAL driver. A DOUBLE PRECISION
column will be created instead. A DATE format will be associated with the column.
NOTE: CASDAL driver. Creation of a DATE column has been requested, but is not supported by the CASDAL driver. A DOUBLE PRECISION
column will be created instead. A DATE format will be associated with the column.
NOTE: Table TESTA was created in caslib MKT with 36688 rows returned.
NOTE: PROCEDURE CAS used (Total process time):
real time 0.75 seconds
cpu time 0.02 seconds

99
100 proc summary data=mkt.testa print ;
101 format _FECULVTO year. ;
102 class from_brand _FECULVTO ;
103 var renewed;
104 output out=test sum= mean= / autoname;
105 run;
NOTE: The CAS aggregation.aggregate action will be used to perform the initial summarization.
ERROR: The analytic variable's data type is not supported.

[Kurt_Bremser]

Since the SQL code complains about a DATE, I think that _FECULVTO is the culprit here.

See if this works:

proc cas;
source MPG_toyota;
create table mkt.testa {options  replication=0 REPLACE=true } as
  select 
    a.from_brand,
    year(a._FECULVTO) as FECULVTO,
    case when b.numbasti=''
      then 0
      else 1
    end as renewed,
    b.CODOPERA_FOR,
    b.NUMBASTI_FOR   
  from public.denom a left join public.nom b
  on a.numbasti=b.numbasti
;
endsource;
fedSQL.execDirect / query=MPG_toyota;
quit;


proc summary data=mkt.testa print;
class from_brand FECULVTO;
var renewed;
output out=test sum= mean= / autoname;
run;

[acordes]

Thanks Kurt.

But that doesn't fix it either. 

[Tom]

What if you add a decimal point? 0.0 and 1.0 instead of 0 and 1?

What if you list explicitly which variables you want instead of using SELECT *?

[acordes]

Thanks Tom,

I've tried selecting only a few variables and 1.0 and 0.0 instead of my then else values. 

The same error. 

I write some extra data step or select case when explicitly as character "1" and "0". This works and I use it in the class statement of the proc summary. 

It's a strange behavior of fedsql I'd say...

[Tom]

In PROC SQL if you try to create two variables with the same name only the first one survives.

Try it.

proc sql;
create table test as
  select name , age as name
  from sashelp.class
;
quit;

I assume FEDSQL will exhibit the same behavior.

Make sure that the name renewed has not already been used by another variable that appears before the CASE expression in your SELECT statement.

[Tom]

Are you sure that you have actually replaced the dataset?

What happens if you use a different dataset name, one that does not already exist.

Do you get a different error message from the PROC SUMMARY step?