Hi Experts,   This query is regarding performing ETL in SAS VIYA plateform.   I need to perform ETL in SAS VIYA(SAS Studio) using all the hive tables. Can I establish a connection between SAS VIYA and HIVE to use source tables which are physically not available in SAS VIYA.   Thanks ... View more

Hi Experts,   I am using below code but its not giving both by group value.   data list; input Name$1-10 Class$ List_price ; datalines; Black Bora Track 796 Black Bora Road 399 Black Bora Track 1130 Black Bora Road 1995 Scirocco Mountain 899 Scirocco Mountain 459 Scirocco Road 2256 Scirocco Road 559 ; run; proc sort data = list out=list1; by Name Class; run; proc print data = list1; run; data final; set list1; by name class; if first.name and first.class then List_price1=List_price; else List_price1=0; run;   Getting attached result. I simply need the List_price for both by group (name and class). HAVE Name Class List_Price Black Bora  Track      796 Black Bora  Track 399 Black Bora  road 1130 Black Bora  Road       1995 Scirocco    Mountain   899 Scirocco    Mountain   459 Scirocco    Road      2256 Scirocco    Road       559   WANT Name Class List_Price Black Bora  Track      796 Black Bora  Track 0 Black Bora  road 1130 Black Bora  Road       0 Scirocco    Mountain   899 Scirocco    Mountain   0 Scirocco    Road      2256 Scirocco    Road       0 thanks   ... View more

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,(CAS E WHEN QCErrorLevelName LIKE 'L%' Then QCErrorLevelName ELSE 'No Error' End) ) A GROUP BY ProjectCode, QCErrorLevelName,Employee_Name ... View more

Hi Experts,   I am trying to group the given data by pcode, empname, mname, team, date in the final data "STAG3 " but not getting the correct result. while, if i don't include date, it gives correct value.   Please look into the below code and correct me if i am making any mistake. Sample data is attached.   Need Grouping by pcode, empname, mname, team, date data fields for CasePercentage . Is there any efficient way to do this?     proc sql ; create table stag1 as select distinct pcode, empname, mname, team, caseid, type, ser, status, date from report_data; quit; proc sql; create table stag2 as select stag1.*, case when status In ('QCCOMP') AND caseid NOT IN ('') THEN '1' else '0' end as Case_ID_for_QCCOMP, case when status In ('QCCOMPERR') AND caseid NOT IN ('') THEN '1' else '0' end as Case_ID_for_QCCOMPERR, case when status In ('QCCOMP','QCCOMPERR') AND caseid NOT IN ('') THEN '1' else '0' end as Total_Case_IDs from stag1; quit; proc sql; create table STAG3 as select distinct pcode, empname, mname, team, (COMP/(COMP+COMPERR))*100 AS CasePercentage from (select distinct pcode, empname, mname, team, sum(input(Case_ID_for_QCCOMP,BEST12.)) as COMP, sum(input(Case_ID_for_QCCOMPERR,BEST12.)) as COMPERR FROM stag2 group by pcode, empname, mname, team ) as test; quit; ... View more