As part of a SAS project, I pulled a query of phone call data that will eventually be joined with other tables. However, in the Team column, there are the following lines: WI TEAM, WI1/IN TEAM, WI2 TEAM and WI3 TEAM. I want to equally divide the counts for the WI TEAM amongst the other three teams and add to their counts. How can I do this in Enterprise Guide? I don't want to export to Excel, edit and then reload back to SAS. I have attached a screen copy of the table.
If you're able to assign departments to teams (eg. via a SAS Format) then things could become rather simple (see code example below).
data have;
infile datalines truncover;
input TEAM $ CALLS_IN CALLS_ANSWRD;
datalines;
WITEAM 9 12
WI1TEAM 10 8
WI2TEAM 6 4
WI3TEAM 8 6
;
run;
data have_enriched;
set have;
department=substrn(team,1,2);
is_department_flg= (substrn(upcase(team),1,2)||'TEAM' = upcase(team));
run;
proc sql;
create table test as
select
a.*,
b.CALLS_IN as dep_CALLS_IN,
b.CALLS_ANSWRD as dep_CALLS_ANSWRD,
count(distinct a.team) as N_teams,
sum(a.CALLS_IN, dep_CALLS_IN/calculated N_teams) as CALLS_IN_plus,
sum(a.CALLS_ANSWRD, dep_CALLS_ANSWRD/calculated N_teams) as CALLS_ANSWRD_plus
from
have_enriched as a
left join
have_enriched as b
on a.department=b.department and b.is_department_flg=1
where a.is_department_flg=0
group by a.Department
;
quit;
Can you provide data in text form? It is hard to run code against a picture 🙂 https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... has some instructions on how to generate data step code that can be pasted here to recreate data. You only need enough rows to demostrate the issue.
And the desired result for the posted example data.
Are any other sets of teams involved in this exercise? Do any of the teams involved have more than one record in your data set?
The solution is likely going to involve code as I suspect this may get beyond the menu items in EG for solutions.
I am having a problem in providing the data code you asked for. In the link it says to load to the Autoexec file, but I have a problem there. I am running on a company server through CITRIX and I don't have administrative rights. I did load the macro as a separate program in my project and linked it to the data table, but I don't think it is giving me what you are requesting for the data code.
if the result looks anything like a data datasetname statement followed by informat, format, input and data then that is what it should look like.
You'd better post your data and output.
Divide your table into two tables.
one contains WI TEAM, WI1/IN TEAM, WI2 TEAM and WI3 TEAM. as X table.
another doesn't contain these team. as Y table.
use proc means to get SUM ,N and the value you want SUM/N, then SET X table back to Y table.
Thanks Ksharp...that's what I have been doing, but I was hoping that there would be an easier method. I appreciate your help.
Thanks!
I think your original way is good enough.
OK. You can do it by SQL. another way is using IML.
data have;
input name $ x1 x2;
cards;
WITEAM 1 2
WI1/IN 3 4
X 3 6
Y 5 6
;
run;
proc sql;
create table want as
select *
from have
where name not in ('WITEAM','WI1/IN')
union all
select name,avg(x1) as x1,avg(x2) as x2
from have
where name in ('WITEAM','WI1/IN');
quit;
Thanks Ksharp...this is close but not quite there. For example, let's say I have the following table:
TEAM CALLS_IN CALLS_ANSWRD
WITEAM 9 12
WI1TEAM 10 8
WI2TEAM 6 4
WI3TEAM 8 6
I want the end table to include the WI1, WI2 and WI3 counts plus 1/3 of the WITEAM addede to each of those counts. So the want table would look like:
TEAM CALLS_IN CALLS_ANSWRD
WI1TEAM 13 12
WI2TEAM 9 8
WI3TEAM 11 10
If you're able to assign departments to teams (eg. via a SAS Format) then things could become rather simple (see code example below).
data have;
infile datalines truncover;
input TEAM $ CALLS_IN CALLS_ANSWRD;
datalines;
WITEAM 9 12
WI1TEAM 10 8
WI2TEAM 6 4
WI3TEAM 8 6
;
run;
data have_enriched;
set have;
department=substrn(team,1,2);
is_department_flg= (substrn(upcase(team),1,2)||'TEAM' = upcase(team));
run;
proc sql;
create table test as
select
a.*,
b.CALLS_IN as dep_CALLS_IN,
b.CALLS_ANSWRD as dep_CALLS_ANSWRD,
count(distinct a.team) as N_teams,
sum(a.CALLS_IN, dep_CALLS_IN/calculated N_teams) as CALLS_IN_plus,
sum(a.CALLS_ANSWRD, dep_CALLS_ANSWRD/calculated N_teams) as CALLS_ANSWRD_plus
from
have_enriched as a
left join
have_enriched as b
on a.department=b.department and b.is_department_flg=1
where a.is_department_flg=0
group by a.Department
;
quit;
Thanks Patrick...this helped and I was able to get what I needed. The only thing else I wanted to do to edit your code is to pull in the "have" data for the "datalines; WITEAM 9 12 WI1TEAM 10 8 WI2TEAM 6 4 WI3TEAM 8 6" instead of manually entering them in to the code. Otherwise, it works perfectly and saves me a lot of time.
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!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.