Partial group and sum up, need help thanks.

[pensarchem]

data have;

input

FITM 1-4 code 5-8 expend 9-20;

datalines;

2888 362 3018.72

2888 367 13616.76

2888 368 19418.4

2888 369 148.14

2888 378 2250.12

2888 930 3788.4

2889 362 7264.04

2889 930 2794.49

2898 362 78699.9

2898 367 468766.92

2898 368 738630.6

2898 369 2249.52

2898 378 171642.18

2898 930 24979.8

2899 362 466443.89

2899 367 13273411.86

2899 368 1104602.37

2899 369 15137.85

2899 378 503299.78

2899 930 6100.54

run;

Given that the above is table1, how to come up with as table2 with SAS code (letting 367,368,369 in one group and sum up)?  Thanks.

Table2 like

  FITM     code        expend

2888      362          $$$

2888      367-369   $$

2888      378          $$$

2888     930          $$$

2889    362....

........

[pensarchem]

data have;

input

FITM 1-4 code 5-8 expend 9-20;

datalines;

2888 362 3018.72

2888 367 13616.76

2888 368 19418.4

2888 369 148.14

2888 378 2250.12

2888 930 3788.4

2889 362 7264.04

2889 930 2794.49

2898 362 78699.9

2898 367 468766.92

2898 368 738630.6

2898 369 2249.52

2898 378 171642.18

2898 930 24979.8

2899 362 466443.89

2899 367 13273411.86

2899 368 1104602.37

2899 369 15137.85

2899 378 503299.78

2899 930 6100.54

run;

Given that the above is table1, how to come up with as table2 with SAS code (letting 367,368,369 in one group and sum up)?  Thanks.

Table2 like

  FITM     code        expend

2888      362          $$$

2888      367-369   $$

2888      378          $$$

2888     930          $$$

2889    362....

........

[pensarchem]

 Fitm       Code      Dollar

2888	362	3018.72
2888	367	13616.76
2888	368	19418.4
2888	369	148.14
2888	378	2250.12
2888	930	3788.4

Given that this is table1, how to come up with as table2 with SAS code (let 367,368,369) in one group?  Thanks.

Table2 like

  FITM     code        dollar

2888	362	3018.72
2888	367-369	33183.3
2888	378	2250.12
2888	930	3788.4

[novinosrin]

Hi @pensarchem   So are you grouping anything continuous as one group and summing them? or a mere hardcoded question for knowledge sharing?

What i mean is

data have;
input var1-var3;
cards;
2888	362	3018.72
2888	367	13616.76
2888	368	19418.4
2888	369	148.14
2888	378	2250.12
2888	930	3788.4
;
 
data w;
set have;
if dif(var2) ne 1 then grp+1;
run;

Now its just a mere lazy summary

[pensarchem]

@novinosrin  Just to clarify, I group them (367,368,369),then sum up.   I hope that you can share the knowledge with me.  I know how to do it under sql, but I don't know sas. thanks. 

I put the header on for each column.

[novinosrin]

Oh well, I don't know how to do that in sql I wish you could post that for me and others to learn

But datastep dif is easier

data have;
input var1-var3;
cards;
2888	362	3018.72
2888	367	13616.76
2888	368	19418.4
2888	369	148.14
2888	378	2250.12
2888	930	3788.4
;
 
data temp;
set have;
if dif(var2) ne 1 then grp+1;
run;

data want;
set temp;
by grp;	
retain newvar '         ';
if first.grp then do; sum=0;newvar=cats(var2);end;
sum+var3;
if last.grp;
if not first.grp then newvar=catx('-',newvar,var2);
drop grp;
run;

[Cynthia_sas · Accepted solution]

Hi:

For a different perspective, I would use PROC MEANS to create an output dataset. The key is a user-defined format that treats the 367, 368, 369 as one group.

Here's the code:

data have;
infile datalines;
input FITM  code  expend ;
datalines;
2888 362 3018.72
2888 367 13616.76
2888 368 19418.4
2888 369 148.14
2888 378 2250.12
2888 930 3788.4
2889 362 7264.04
2889 930 2794.49
2898 362 78699.9
2898 367 468766.92
2898 368 738630.6
2898 369 2249.52
2898 378 171642.18
2898 930 24979.8
2899 362 466443.89
2899 367 13273411.86
2899 368 1104602.37
2899 369 15137.85
2899 378 503299.78
2899 930 6100.54
;
run;

proc format;
  value codef 367-369='367-369';
run;

title 'summarizing with PROC MEANS';
proc means data=have sum nway;
  class FITM code;
  var expend;
  output out=work.meanout sum=expend;
  format code codef.;
run;

proc print data=meanout;
  title 'Using PROC MEANS';
run;
title;

Here's the output dataset work.meanout shown in PROC PRINT:

Of course you can drop _TYPE_ and _FREQ_ after you validate the results.

Cynthia

[pensarchem]

thank you very much!!

[pensarchem]

@novinosrin   for your reference. thanks.

select fitm,
          case

               when code in ('362') then '362'
               when code in ('367','368','369','378') then '367 to 378'
                else '930' end ,        
        sum(dollar) as expend

from table1

group by fitm,        

               case when code in ('362') then '362'
               when code in ('367','368','369','378') then '367 to 378'
               else '930' end

order by  fitm;

[pensarchem]

Sorry @novinosrin , your program also works very well after tried it just now. I did not understand  it yesterday. Thanks again and best wishes.

[ScottBass]

Edit your post with self-contained data steps using datalines.  Don't make us do your job converting your post into usable code.

[pensarchem]

edited the data, thanks. 

[ScottBass]

Try again.  Cut-and-paste your data into SAS.  Does it give the correct results?  For me, I get a bunch of missing records.

Hint: Wrap your code via "Insert SAS Code".

[ScottBass]

I'm too impatient.  Do this:

• Create a format to collapse data as desired.
• Use PROC SUMMARY to collapse your data using formatted input.

Here's some test code.  I leave it to you to research PROC SUMMARY and its support of formats to create groups.

data have;
   length code 8;
   input code;
   datalines;
362
367
368
369
378
930
362
930
362
367
368
369
378
930
362
367
368
369
378
930
;
run;

proc format;
   value code
      367-369='367-369'
   ;
run;

data want;
   set have;
   group=put(code,code.-L);
run;

[Cynthia_sas]

Hi:
There's no need to post the same question in 2 different forums. There have been other answers posted here: https://communities.sas.com/t5/New-SAS-User/rookie-asks-for-help/m-p/557649/highlight/false#M10004

Cynthia

[ScottBass]

Thanks @Cynthia_sas .  I'm unsubscribing from this one 🙂