Desktop productivity for business analysts and programmers

Equal shares of one row of data amongst three other rolls of data

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Equal shares of one row of data amongst three other rolls of data

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.

Attachment

Accepted Solutions
Solution
‎10-13-2016 04:12 PM
Respected Advisor
Posts: 3,831

Re: Equal shares of one row of data amongst three other rolls of data

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


All Replies
Grand Advisor
Posts: 10,210

Re: Equal shares of one row of data amongst three other rolls of data

Can you provide data in text form? It is hard to run code against a picture Smiley Happy 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.

Occasional Contributor
Posts: 9

Re: Equal shares of one row of data amongst three other rolls of data

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.

Grand Advisor
Posts: 10,210

Re: Equal shares of one row of data amongst three other rolls of data

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.

 

 

 

Occasional Contributor
Posts: 9

Re: Equal shares of one row of data amongst three other rolls of data

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!

Grand Advisor
Posts: 9,576

Re: Equal shares of one row of data amongst three other rolls of data

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.

Occasional Contributor
Posts: 9

Re: Equal shares of one row of data amongst three other rolls of data

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!

Grand Advisor
Posts: 9,576

Re: Equal shares of one row of data amongst three other rolls of data

I think your original way is good enough.

Grand Advisor
Posts: 9,576

Re: Equal shares of one row of data amongst three other rolls of data

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;
Occasional Contributor
Posts: 9

Re: Equal shares of one row of data amongst three other rolls of data

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

 

Solution
‎10-13-2016 04:12 PM
Respected Advisor
Posts: 3,831

Re: Equal shares of one row of data amongst three other rolls of data

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;

Occasional Contributor
Posts: 9

Re: Equal shares of one row of data amongst three other rolls of data

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. 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

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