topic Re: converting the query for sas in SAS Programming

converting the query for sas

Rahul_SAS — Tue, 14 Aug 2018 06:53:03 GMT

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

Re: converting the query for sas

SASKiwi — Tue, 14 Aug 2018 07:23:33 GMT

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;

Re: converting the query for sas

Swagat711 — Tue, 14 Aug 2018 07:25:16 GMT

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

Re: converting the query for sas

Kurt_Bremser — Tue, 14 Aug 2018 07:31:45 GMT

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).

Re: converting the query for sas

s_lassen — Tue, 14 Aug 2018 07:27:40 GMT

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;

Re: converting the query for sas

Rahul_SAS — Tue, 14 Aug 2018 08:06:34 GMT

thank you s_lassen. Its working fine

Re: converting the query for sas

Rahul_SAS — Tue, 14 Aug 2018 17:29:43 GMT

s_lassen,

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

thanks