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

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

Hansdewit — Sat, 06 Mar 2021 20:32:16 GMT

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?

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

BrunoMueller — Mon, 08 Mar 2021 07:50:16 GMT

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;

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

Hansdewit — Mon, 08 Mar 2021 08:43:47 GMT

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.

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

Hansdewit — Mon, 08 Mar 2021 08:51:11 GMT

i see now the procedures cannot handle int32 and int64.

https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.3&docsetId=pgmdiff&docsetTarget=n0b6wc0bp6tt31n1l98k9g2l2l9b.htm&locale=en

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

BrunoMueller — Mon, 08 Mar 2021 09:11:44 GMT

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=n0b6wc0bp6tt31n1l98k9g2l2l9b.htm&locale=en