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
Does the query run without macro variables?
Hi Chris,
No change when substituting a date literal vs a macro variable.
--Ben
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.