BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
gregor1
Quartz | Level 8

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.


Call data by Team.JPG
1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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;

View solution in original post

11 REPLIES 11
ballardw
Super User

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.

gregor1
Quartz | Level 8

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.

ballardw
Super User

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.

 

 

 

gregor1
Quartz | Level 8
Yes, I know, but the output does not look like that there is no input nor data...I will keep trying to get what you need.



Thanks!

Ksharp
Super User

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.

gregor1
Quartz | Level 8

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!

Ksharp
Super User

I think your original way is good enough.

Ksharp
Super User

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;
gregor1
Quartz | Level 8

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

 

Patrick
Opal | Level 21

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;

gregor1
Quartz | Level 8

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 1359 views
  • 1 like
  • 4 in conversation