topic Re: rookie asks for help in SAS Programming

Partial group and sum up, need help thanks.

pensarchem — Fri, 10 May 2019 00:23:28 GMT

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

........

rookie asks for help

pensarchem — Thu, 09 May 2019 23:31:45 GMT

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

Re: rookie asks for help

novinosrin — Thu, 09 May 2019 23:27:48 GMT

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

Re: rookie asks for help

pensarchem — Thu, 09 May 2019 23:29:51 GMT

@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.

Re: rookie asks for help

novinosrin — Thu, 09 May 2019 23:39:46 GMT

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;

Re: Partial group and sum up, need help thanks.

ScottBass — Thu, 09 May 2019 23:57:28 GMT

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

Re: Partial group and sum up, need help thanks.

pensarchem — Fri, 10 May 2019 00:24:12 GMT

edited the data, thanks.

Re: Partial group and sum up, need help thanks.

ScottBass — Fri, 10 May 2019 00:28:13 GMT

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".

Re: Partial group and sum up, need help thanks.

ScottBass — Fri, 10 May 2019 01:26:35 GMT

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;

Re: rookie asks for help

Cynthia_sas — Fri, 10 May 2019 01:32:23 GMT

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

Re: Partial group and sum up, need help thanks.

Cynthia_sas — Fri, 10 May 2019 01:33:32 GMT

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

Re: Partial group and sum up, need help thanks.

ScottBass — Fri, 10 May 2019 01:34:58 GMT

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

Re: rookie asks for help

pensarchem — Fri, 10 May 2019 16:24:31 GMT

thank you very much!!

Re: rookie asks for help

pensarchem — Fri, 10 May 2019 16:29:13 GMT

@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;

Re: rookie asks for help

pensarchem — Fri, 10 May 2019 16:40:30 GMT

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