DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

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

I have a dataset that looks like this:

YearIndustyEmp
20010111
20020112
20010101
20010123
20020121
20010204
20020202

 

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:

 

YearIndustyEmp
20010111
20020112
20010101
20010123
20020121
20010204
20020202
2001015
2002013
2001024
2002022
2001A9
2002A5

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     

 

View solution in original post


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     

 

Esteemed Advisor
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.

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

Discussion stats
  • 2 replies
  • 101 views
  • 0 likes
  • 3 in conversation