I have a data like this :
data one;
infile datalines;
input Group_Name: $12. Head_Name :$12. LastYear_Count BeforeLastYear_Count;
datalines;
R&D IT 1 2
R&D Testing 0 5
R&D Production 4 0
R&D Development 1 0
Sales Book 5 4
Sales Product 10 0
Sales Kit 1 1
Admin Marketing 4 1
Admin HR 5 2
Admin Project 6 3
;
run;
I need the output like this:
Group_Name | Head_Name | LastYear_Count | BeforeLastYear_Count |
R&D | IT | 1 | 2 |
R&D | Testing | 0 | 5 |
R&D | Production | 4 | 0 |
R&D | Development | 1 | 0 |
SubTotal | 6 | 7 | |
Sales | Book | 5 | 4 |
Sales | Product | 10 | 0 |
Sales | Kit | 1 | 1 |
SubTotal | 16 | 5 | |
Admin | Marketing | 4 | 1 |
Admin | HR | 5 | 2 |
Admin | Project | 6 | 3 |
SubTotal | 15 | 6 |
data one;
infile datalines;
input Group_Name: $12. Head_Name :$12. LastYear_Count BeforeLastYear_Count;
datalines;
R&D IT 1 2
R&D Testing 0 5
R&D Production 4 0
R&D Development 1 0
Sales Book 5 4
Sales Product 10 0
Sales Kit 1 1
Admin Marketing 4 1
Admin HR 5 2
Admin Project 6 3
;
run;
proc sort data=one;
by Group_Name;
run;
data want;
set one;
by Group_Name;
output;
retain LastYear_Count_ BeforeLastYear_Count_;
if first.Group_Name then do; LastYear_Count_=.;BeforeLastYear_Count_=.;end;
LastYear_Count_+LastYear_Count;
BeforeLastYear_Count_+BeforeLastYear_Count;
Group_Name='Sub Total';
if last.Group_Name then do;LastYear_Count=LastYear_Count_;BeforeLastYear_Count=BeforeLastYear_Count_;
output;
end;
drop BeforeLastYear_Count_ LastYear_Count_;
run;
I believe it is giving the expected output the only update is with regard to making Head_Name='';
could you please check now and let me know.
I know you got the solution through proc report but would like to know if this also works.
data want;
set one;
by Group_Name;
output;
retain LastYear_Count_ BeforeLastYear_Count_;
if first.Group_Name then do; LastYear_Count_=.;BeforeLastYear_Count_=.;end;
LastYear_Count_+LastYear_Count;
BeforeLastYear_Count_+BeforeLastYear_Count;
Group_Name='Sub Total';
Head_Name='';
if last.Group_Name then do;LastYear_Count=LastYear_Count_;BeforeLastYear_Count=BeforeLastYear_Count_;
output;
end;
drop BeforeLastYear_Count_ LastYear_Count_;
run;
Please use the search functionality before posting, there are hundreds of posts already on this topic. This one from last week for instance:
Hi ,
data one;
infile datalines;
input Group_Name: $12. Head_Name :$12. LastYear_Count BeforeLastYear_Count;
datalines;
R&D IT 1 2
R&D Testing 0 5
R&D Production 4 0
R&D Development 1 0
Sales Book 5 4
Sales Product 10 0
Sales Kit 1 1
Admin Marketing 4 1
Admin HR 5 2
Admin Project 6 3
;
run;
proc report data=one out=want1;
column group_name Head_Name LastYear_Count BeforeLastYear_Count;
define group_name/order;
define head_name/display;
define LastYear_Count/sum;
define BeforeLastYear_Count/sum;
break after group_name/summarize;
run;
data final (drop=_break_);
set want1;
by group_name ;
if last.group_name then group_name='totel';
run;
Thanks 🙂
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.