Solved
Contributor
Posts: 23

# Creating new rows in an existing dataset based on sums of groups

I have a dataset that looks like this:

 Year Industy Emp 2001 011 1 2002 011 2 2001 010 1 2001 012 3 2002 012 1 2001 020 4 2002 020 2

I have to create new rows based on these groups:

01=011,010,012

02=020

A=01,02

I need to sum over the groups by year so that the final data set loos as this:

 Year Industy Emp 2001 011 1 2002 011 2 2001 010 1 2001 012 3 2002 012 1 2001 020 4 2002 020 2 2001 01 5 2002 01 3 2001 02 4 2002 02 2 2001 A 9 2002 A 5

Accepted Solutions
Solution
‎06-11-2018 11:22 PM
PROC Star
Posts: 2,370

## Re: Creating new rows in an existing dataset based on sums of groups

Like this?

``````data HAVE;
input YEAR INDUSTRY \$ EMP;
cards;
2001 011 1
2002 011 2
2001 010 1
2001 012 3
2002 012 1
2001 020 4
2002 020 2
run;

proc format;
value \$indgrp (multilabel) '010'-'012'             ='01'
'020'                   ='02'
'010'-'019','020'-'029' ='A' ;
run;

proc summary data=HAVE nway ;
class INDUSTRY  /mlf;
class YEAR ;
var EMP;
format INDUSTRY \$indgrp.;
output out=SUMS(keep=YEAR INDUSTRY EMP) sum=;
run;

data WANT;
set HAVE SUMS(in=S);
format INDUSTRY ;
if S then INDUSTRY=put(INDUSTRY, \$indgrp.);
run;
``````

YEAR INDUSTRY EMP
2001 011      1
2002 011      2
2001 010      1
2001 012      3
2002 012      1
2001 020      4
2002 020      2
2001 01       5
2002 01       3
2001 02       4
2002 02       2
2001 A        9
2002 A        5

All Replies
Solution
‎06-11-2018 11:22 PM
PROC Star
Posts: 2,370

## Re: Creating new rows in an existing dataset based on sums of groups

Like this?

``````data HAVE;
input YEAR INDUSTRY \$ EMP;
cards;
2001 011 1
2002 011 2
2001 010 1
2001 012 3
2002 012 1
2001 020 4
2002 020 2
run;

proc format;
value \$indgrp (multilabel) '010'-'012'             ='01'
'020'                   ='02'
'010'-'019','020'-'029' ='A' ;
run;

proc summary data=HAVE nway ;
class INDUSTRY  /mlf;
class YEAR ;
var EMP;
format INDUSTRY \$indgrp.;
output out=SUMS(keep=YEAR INDUSTRY EMP) sum=;
run;

data WANT;
set HAVE SUMS(in=S);
format INDUSTRY ;
if S then INDUSTRY=put(INDUSTRY, \$indgrp.);
run;
``````

YEAR INDUSTRY EMP
2001 011      1
2002 011      2
2001 010      1
2001 012      3
2002 012      1
2001 020      4
2002 020      2
2001 01       5
2002 01       3
2001 02       4
2002 02       2
2001 A        9
2002 A        5

Posts: 5,540

## Re: Creating new rows in an existing dataset based on sums of groups

Or this:

``````data HAVE;
input YEAR INDUSTRY \$ EMP;
cards;
2001 011 1
2002 011 2
2001 010 1
2001 012 3
2002 012 1
2001 020 4
2002 020 2
;

data groups;
input group \$ industry \$;
datalines;
01 011
01 010
01 012
02 020
A 011
A 010
A 012
A 020
;

proc sql;
create table want as
select year, industry, emp from have
union all
select year, group as industry, sum(emp) as emp
from have inner join groups on have.industry=groups.industry
group by group, year;
quit;
``````
PG
☑ This topic is solved.