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....
........
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
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 |
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
@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.
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;
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
thank you very much!!
@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;
Sorry @novinosrin , your program also works very well after tried it just now. I did not understand it yesterday. Thanks again and best wishes.
Edit your post with self-contained data steps using datalines. Don't make us do your job converting your post into usable code.
edited the data, thanks.
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".
I'm too impatient. Do this:
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;
Thanks @Cynthia_sas . I'm unsubscribing from this one 🙂
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: