topic Re: struggling with formats used in fedsql in SAS Programming

struggling with formats used in fedsql

acordes — Thu, 31 Mar 2022 14:03:35 GMT

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;

Re: struggling with formats used in fedsql

tarheel13 — Thu, 31 Mar 2022 14:19:29 GMT

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.

Re: struggling with formats used in fedsql

ballardw — Thu, 31 Mar 2022 14:30:33 GMT

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

Re: struggling with formats used in fedsql

acordes — Thu, 31 Mar 2022 15:16:45 GMT

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;

Re: struggling with formats used in fedsql

Kurt_Bremser — Thu, 31 Mar 2022 15:23:50 GMT

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
;

Re: struggling with formats used in fedsql

acordes — Thu, 31 Mar 2022 15:27:32 GMT

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.

Re: struggling with formats used in fedsql

Kurt_Bremser — Thu, 31 Mar 2022 15:38:36 GMT

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;

Re: struggling with formats used in fedsql

acordes — Thu, 31 Mar 2022 15:55:05 GMT

Thanks Kurt.

But that doesn't fix it either.

Re: struggling with formats used in fedsql

Tom — Thu, 31 Mar 2022 16:00:09 GMT

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 *?

Re: struggling with formats used in fedsql

acordes — Thu, 31 Mar 2022 16:10:40 GMT

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

Re: struggling with formats used in fedsql

Tom — Thu, 31 Mar 2022 16:52:34 GMT

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.

Re: struggling with formats used in fedsql

Tom — Thu, 31 Mar 2022 16:54:43 GMT

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?