How to use proc sql in SAS Data Studio Viya?

Hansmuffs — Sun, 05 Jan 2020 13:06:49 GMT

Hey Guys,

in my next job I have to work with SAS Visual Analytics because of this I have created a Trial Account on SAS Viya. At the SAS Data Studio in Transformation Code (CASL Editor), I have a problem with a subquery. I have created the following query in FedSQL.

loadActionSet 'fedsql';
queryCode = 'create table "'||_dp_outputCaslib||'"."'||_dp_outputTable||'" {options replace=true}
as select ID, Stadt, Stadt_upper, Einwohner, Bundesland, 
(Select sum(Einwohner) from "'||_dp_inputCaslib||'"."'||_dp_inputTable||'" as b where b.Bundesland = a.Bundesland) as Sum_Bundesland
from "'||_dp_inputCaslib||'"."'||_dp_inputTable||'" as a'; 
print queryCode;
ExecDirect / query=queryCode;

And i receive the following error message...

The action was not successful
ERROR: Unsupported operation in FedSQL query: Correlated subquery.

After this message I change from FedSQL to PROC SQL.

loadActionSet 'proc sql';
queryCode = 'create table "'||_dp_outputCaslib||'"."'||_dp_outputTable||'" {options replace=true}
as select ID, Stadt, Stadt_upper, Einwohner, Bundesland, 
(Select sum(Einwohner) from "'||_dp_inputCaslib||'"."'||_dp_inputTable||'" as b where b.Bundesland = a.Bundesland) as Sum_Bundesland
from "'||_dp_inputCaslib||'"."'||_dp_inputTable||'" as a'; 
print queryCode;
ExecDirect / query=queryCode;

This is the next error message...

The action was not successful
ERROR: Load access denied for user xxx.xxx@xxx.de on action set _UNREGISTERED.
ERROR: Action set 'proc sql' was not loaded due to errors.

What do I have to do, to use subquerys in the transformation code Step in SAS Data Studio?

Regards and thank you...

Re: How to use proc sql in SAS Data Studio Viya?

Patrick — Sun, 05 Jan 2020 21:49:35 GMT

Only FedSQL has been implemented in CAS. The FedSQL SQL implementation only supports non-correlated queries as documented here.

From a coding perspective: Depending on the Viya version there are many things for which you don't need action sets but can use more "traditional" SAS syntax. For what you're doing the following two coding approaches should work (not tested).

proc fedsql sessref=mysess;
  create table _dp_outputCaslib._dp_outputTable {options replace=true} as
  select 
    id, 
    stadt, 
    stadt_upper, 
    einwohner, 
    bundesland,
    sum(Einwohner) as Sum_Bundesland
    from _dp_inputCaslib._dp_inputTable
    group by 
    id, 
    stadt, 
    stadt_upper, 
    einwohner, 
    bundesland    
  ;

data _dp_outputCaslib._dp_outputTable;
  set _dp_inputCaslib._dp_inputTable;
  by Bundesland;
  keep id stadt stadt_upper einwohner bundesland sum_bundesland;
  retain sum_bundesland;
  if first.bundesland then sum_bumdesland=0;
  sum_bumdesland=sum(sum_bumdesland,Einwohner);
run;

topic Re: How to use proc sql in SAS Data Studio Viya? in New SAS User

How to use proc sql in SAS Data Studio Viya?

Re: How to use proc sql in SAS Data Studio Viya?