converting the query for sas

[Rahul_SAS]

Hi Experts,

Please help me to convert the following query in sas query.

I tried but getting errors.

SELECT Employee_Name, ProjectCode, QCErrorLevelName,

CAST(SUM(Perc)*100 as VARCHAR)+'%' Error_Rate FROM

(

select CAST(COUNT(DISTINCT SafetyCaseID) as FLOAT)/

CAST((select COUNT(Distinct SafetyCaseID)

from dbo.VWCDSCPQualityMetricsRpt P1

where P1.ProjectCode = P.ProjectCode and P1.Employee_Name = P.Employee_Name

GROUP BY ProjectCode,Employee_Name) as FLOAT) as Perc

,ProjectCode,Employee_Name, (CASE WHEN QCErrorLevelName LIKE 'L%' Then QCErrorLevelName ELSE 'No Error' End) QCErrorLevelName

from dbo.VWCDSCPQualityMetricsRpt P

where QCErrorLevelName LIKE 'L%'

GROUP BY ProjectCode,Employee_Name,(CASE WHEN QCErrorLevelName LIKE 'L%' Then QCErrorLevelName ELSE 'No Error' End)

) A

GROUP BY ProjectCode, QCErrorLevelName,Employee_Name

[SASKiwi]

The easiest solution is SQL passthrough - you will need to add your own database connection options:

proc sql;
  connect to odbc (** put your connection options here **);
  create table want as 
  select  *
  from connection to odbc
( 
 SELECT Employee_Name, ProjectCode, QCErrorLevelName,
CAST(SUM(Perc)*100 as VARCHAR)+'%' Error_Rate FROM
(
select CAST(COUNT(DISTINCT SafetyCaseID) as FLOAT)/
CAST((select COUNT(Distinct SafetyCaseID)
from dbo.VWCDSCPQualityMetricsRpt P1
where P1.ProjectCode = P.ProjectCode and P1.Employee_Name = P.Employee_Name
GROUP BY ProjectCode,Employee_Name) as FLOAT) as Perc
,ProjectCode,Employee_Name, (CASE WHEN QCErrorLevelName LIKE 'L%' Then QCErrorLevelName ELSE 'No Error' End) QCErrorLevelName
from dbo.VWCDSCPQualityMetricsRpt P
where QCErrorLevelName LIKE 'L%'
GROUP BY ProjectCode,Employee_Name,(CASE WHEN QCErrorLevelName LIKE 'L%' Then QCErrorLevelName ELSE 'No Error' End)
) A
GROUP BY ProjectCode, QCErrorLevelName,Employee_Name

);
quit;

[Swagat711]

You can use pass through for running the query as it is in sas.

You will get the Path and Authdomain from Library details or libname details for pass through query

[Kurt_Bremser]

Start by giving this awful eyesore of spaghetti code some visual formatting, so you can see functional blocks.

Replace the CAST functions with put where needed (eg the cast to float is not needed, as all numbers in SAS are real numbers).

[s_lassen]

I think it is mostly getting rid of the CAST calls, as they are not in SAS SQL. The CASTs to FLOAT are unnecessary, as SAS numbers are floats anyway. The CAST to a string is not necessary, just put a format on the number to display it as a percent:

SELECT 
  Employee_Name, 
  ProjectCode, 
  QCErrorLevelName,
  SUM(Perc) format=percent7.2 as Error_Rate 
FROM
  (select 
     COUNT(DISTINCT SafetyCaseID)/
              (select COUNT(Distinct SafetyCaseID) from dbo.VWCDSCPQualityMetricsRpt P1
               where P1.ProjectCode = P.ProjectCode and P1.Employee_Name = P.Employee_Name
               GROUP BY ProjectCode,Employee_Name) as Perc,
     ProjectCode,
     Employee_Name, 
     CASE 
       WHEN QCErrorLevelName LIKE 'L%' Then QCErrorLevelName 
       ELSE 'No Error' 
     End as QCErrorLevelName
   from dbo.VWCDSCPQualityMetricsRpt P
   where QCErrorLevelName LIKE 'L%'
   GROUP BY ProjectCode,Employee_Name,(CASE WHEN QCErrorLevelName LIKE 'L%' Then QCErrorLevelName ELSE 'No Error' End)
   ) A
GROUP BY ProjectCode, QCErrorLevelName,Employee_Name;

[Rahul_SAS]

thank you s_lassen. Its working fine 

[Rahul_SAS]

s_lassen,

Still, there is some problem in the converted query. Its not giving the correct result as sql query is giving.

thanks