output of a fedsql creates a int32 data type. proc means got an error because int32 in viya 3.5

[Hansdewit]

Hi,

I am working with Sas Viya 3.5.

i created this fedsql .  the calculate variables NO_TARGET1, NO_TARGE0 and  NO_CONNECTIONS become a datatype int32 and int64.

proc fedsql sessref=RAN_Insight;
drop table xx.connt_s1 force;
create table xx.connt_s1 as
SELECT x.RI_DATE
, x.dd
, x.dd2
, sum(case when target=1 then 1 else 0 end) as no_target1
, sum(case when target=0 then 1 else 0 end) as no_target0
, count(*) as no_connections
FROM xx.LAG_4G_weak x
inner join xx.connt_s y on (x.dd= y.dd)
Group by x.RI_DATE, x.dd, x.dd2;
QUIT;

when I execute a proc means on the result of this fedsql, I got an error that 'ERROR: The analytic variable's data type is not supported.'

How can I force in the fedsql that the 'int32' variables become double?

[BrunoMueller · Accepted solution]

You can use the CAST function to convert data types, see example below:

proc fedsql sessref=sugus;
drop table casuser.cars_aggr force;
create table casuser.cars_aggr as
  select
    type
    , sum(case when origin = 'Europe' then 1 else 0 end)::double as eur_cars
    , cast(sum(case when origin = 'Europe' then 1 else 0 end) as  double) as eur_cars2
  from
    casuser.cars
  group by
    type
  ;
quit;

[Hansdewit]

that solve the problem. But isn't it strange that proc means cannot handle int32 data type. i cannot in the documentation that the data type should be double.

[Hansdewit]

i see now the procedures cannot handle int32 and int64. 

https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.3&docsetId=pgmdiff&docsetTarget=n0b6...

[BrunoMueller]

Just a note your are looking at the SAS Viya 3.3 doc, the 3.5 doc is here:
https://go.documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=pgmdiff&docsetTarget=n...