topic Re: SQL Subquery to SAS DATA STEP in SAS Programming

SQL Subquery to SAS DATA STEP

Rahul_SAS — Fri, 15 Feb 2019 15:54:11 GMT

Hi Experts,

Is it possible to convert below mentioned sql code to data step. i can't use sql in my coding. Please help.

proc sql;
create table safety.test as
select distinct project, employee, supervisor, team, qcdate, hub, COMP, COMPERR,
sum(COMP,COMPERR) as sumcomp, (COMP/(COMP+COMPERR)) AS CasePercentage
from
(select distinct project, employee, supervisor, team, qcdate, hub, sum(input(Case_ID_for_QCCOMP,BESt12.)) as COMP, sum(input(Case_ID_for_QCCOMPERR,BEST12.)) as COMPERR
FROM safety.stag3
group by project, employee, supervisor, team, qcdate, hub
)
quit;

Thanks

Re: SQL Subquery to SAS DATA STEP

ballardw — Fri, 15 Feb 2019 16:26:19 GMT

I would use a combination of data step and summary procedure such as:

data start;
   set safety.stag3;
   comp = input(Case_ID_for_QCCOMP,BESt12.);
   comperr = input(Case_ID_for_QCCOMPERR,BESt12.);
run;

proc summary data=start nway;
   class project employee supervisor team qcdate hub;
   var comp comperr;
   output out=startsum sum=;
run;

data want;
   set startsum;
   sumcomp = sum(comp,comperr);
   casepercentage = comp/sumcomp;
run;

I will say I find wanting to add with variables with something like Case_ID in the name looks a tad odd though.