topic Re: FedSQL error Initiating Filter in SAS Programming

FedSQL error Initiating Filter

bconner — Fri, 24 May 2019 16:07:33 GMT

Good morning,

I've converted a working update statement from SQL Server to SAS and am trying to get it to run in FedSQL. Am getting an error I haven't seen before and can't find much in terms of references:

2003 proc fedsql;
2004    update Sac3
2005    set NHHF = ((select sum(Sales_Count)
2006                 from SalesFiles
2007                 where filename in (select filename from FilesV)
2008                   AND receivedate <= &fedend
SYMBOLGEN: Macro variable FEDEND resolves to DATE'2019-05-13'
2009                   AND SalesType = SAC3.EncounterType
2010                   AND MCO = 'West')    -
2011                (select sum(case
2012                            when (Sales_Count is NULL or Sales_Count = 0)
2013                            then Manufacturing_Count
2014                            else Sales_Count
2015                            End)
2016                 from SalesFiles
2017                 WHERE filename in (select filename from FilesV)
2018                   AND SalesType = SAC3.EncounterType
2019                   AND MCO = 'West') )
2020                   /
2021               nullif((select sum(Sales_Count)
2022                       from SalesFiles
2023                       WHERE filename in (select filename from FilesV)
2024                         AND SalesType = SAC3.EncounterType
2025                         AND MCO = 'West'),0)*100.00
2026    Where DataElement = 'Sales';
ERROR: Error occurred initiating Filter.
ERROR: General error
2027 quit;

Any suggestions?

Thanks!

--Ben

Re: FedSQL error Initiating Filter

ChrisNZ — Sun, 26 May 2019 06:34:22 GMT

Does the query run without macro variables?

Re: FedSQL error Initiating Filter

bconner — Mon, 27 May 2019 17:01:22 GMT

Hi Chris,

No change when substituting a date literal vs a macro variable.

--Ben

Re: FedSQL error Initiating Filter

ChrisNZ — Mon, 27 May 2019 22:22:30 GMT

OK, I tried to simplify your query until I found what generates a General error.

The first query runs fine, the second one runs too but the log shows:

NOTE: General error


proc fedsql;
update CLASS
set AGE = ( select mean(max(WEIGHT,HEIGHT))
            from CLASS
            where NAME like 'B%' 
          )    
where NAME like 'J%';
quit;

proc fedsql;
update CLASS
set AGE = ( select mean(case
                        when WEIGHT in (.,0)
                        then HEIGHT
                        else WEIGHT
                        end)
            from CLASS
            where NAME like 'B%' 
          )    
where NAME like 'J%';
quit;

I don't see why this should be. Tech support is your best hope now I reckon.