IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

SUM of values in SAS


IBM Mainframe Forums -> All Other Mainframe Topics
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
rikdeb

New User


Joined: 19 Jan 2009
Posts: 63
Location: hyderabad

PostPosted: Wed Dec 10, 2014 2:37 am
Reply with quote

Hi All..Again in search of some suggestions/help in sas.
I have a file like below
Code:
ID no(10 bytes) (Code)   (Value)
1904129320      S5PA     10
1904129320      S5TA     20

1904129330      S5TA     30

I want to add in such a way that all the record remains as is , ie, my expected output is

Code:
1904129320      S5PA     0
1904129320      S5TA     30  <= Adding up the totals for same ID NO
                                               (sum of values,30=10+20)
1904129330      S5TA     30  <= Being a single record, summed as is


I tried with First and last logic , but it is not giving as expected.
My code i was trying with was
Code:
 If FIRST.IDNO
     AMT=0;
     AMT+VALUES;
  iF LAST.CODE THEN OUTPUT.

Although it is summing up the value, but it is showing the value 10 in the first row and 30 in the second. I do not want 10 to get printed. Instead there will be only one amount if the field Code has multiple values.
Could you please suggest.
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8696
Location: Dubuque, Iowa, USA

PostPosted: Wed Dec 10, 2014 4:22 am
Reply with quote

You have FIRST.IDNO and LAST.CODE -- which rarely works as expected. When you use FIRST. and LAST. it is assumed that your data is sorted by the variable; using FIRST.IDNO and LAST.CODE you need to determine what to sort on first and then second -- or you made a typo and need to change LAST.CODE to LAST.IDNO.
Back to top
View user's profile Send private message
rikdeb

New User


Joined: 19 Jan 2009
Posts: 63
Location: hyderabad

PostPosted: Wed Dec 10, 2014 8:53 am
Reply with quote

Hi Robert..
Yes. i have sorted the file on IDNO and CODE. Generally we use First. and last., but here since my intention is to keep all the records, (like all the records with IDNO and CODE) , so i just tried using FIRST.IDNO and LAST.CODE. It worked, but not totally to my expectation.
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales

PostPosted: Wed Dec 10, 2014 3:19 pm
Reply with quote

As Robert has said you need to change LAST.CODE to LAST.IDNO
Back to top
View user's profile Send private message
rikdeb

New User


Joined: 19 Jan 2009
Posts: 63
Location: hyderabad

PostPosted: Wed Dec 10, 2014 4:01 pm
Reply with quote

Hi,
I already tried that. That will sum the values and the OP will have single record.But i want to retain the records and Output the sum in the last record.
This is what i am trying to do:
1. Input
Code:
ID no(10 bytes) (Code)   (Value)
1904129320      S5PA     10
1904129320      S5TA     20

1904129330      S5TA     30


Expected OP:
Code:
(I want to retain the record S5PA and output the sum (value of S5PA+S5tA)
1904129320      S5PA     
1904129320      S5TA     30  <= Adding up the totals for same ID NO
                                               (sum of values,30=10+20)
1904129330      S5TA     30  <= Being a single record, summed as is

With my code:
If FIRST.IDNO
AMT=0;
AMT+VALUES;
iF LAST.CODE THEN OUTPUT.

OP i am getting is:
Code:
1904129320      S5PA     10  <= it is displaying the value here as well and i dnt want this value to be displayed.Instead it can have spaces or zero and sum to be displayed in the last record of IDNO
1904129320      S5TA     30  <= Adding up the totals for same ID NO
                                               (sum of values,30=10+20)
1904129330      S5TA     30  <= Being a single record, summed as is
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8696
Location: Dubuque, Iowa, USA

PostPosted: Wed Dec 10, 2014 5:15 pm
Reply with quote

You need to think through what you want to do -- I suspect OUTPUT is not the way to go as it places ALL variables in the output data. Try something like this (untested):
Code:
DATA X;
SET Y;
BY IDNO ;
IF FIRST.IDNO THEN TOTAMT = 0 ;
TOTAMT + VALUES ;
IF LAST.IDNO THEN PUT ID CODE TOTAMT ;
ELSE PUT ID CODE ;
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales

PostPosted: Wed Dec 10, 2014 5:23 pm
Reply with quote

That's because your code is telling it to do that - It's a simple piece of code to do what you want
Code:
data out02 (drop=amt);
 set out01;
 By idno;
 if first.idno then amt = 0;
 amt + Amnt;
 Amnt = 0;
 If last.idno then amnt = amt;
 run;

proc print noobs; run;
Output is
Code:

1904129320    S5PA      0
1904129320    S5TA     30
1904129330    S5TA     30
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8696
Location: Dubuque, Iowa, USA

PostPosted: Thu Dec 11, 2014 5:58 am
Reply with quote

PROC SUMMARY, PROC UNIVARIATE, PROC MEANS would all probably be preferred over what you're doing -- a PROC tends to be a better solution than a DATA step in SAS, but sometimes there are restrictions that keep a PROC from working.
Back to top
View user's profile Send private message
rikdeb

New User


Joined: 19 Jan 2009
Posts: 63
Location: hyderabad

PostPosted: Thu Dec 11, 2014 8:36 am
Reply with quote

Hi.. Expat.. Thanks ton again!. That was what i was trying to do..
Hi Robert.. I will try the PROCS. Many thanks for your suggestions and time!
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> All Other Mainframe Topics

 


Similar Topics
Topic Forum Replies
No new posts INCLUDE OMIT COND for Multiple values... DFSORT/ICETOOL 5
No new posts Replace Multiple Field values to Othe... DFSORT/ICETOOL 12
No new posts Null values are considered in Total c... DFSORT/ICETOOL 6
No new posts Converting ASCII values to COMP-3 (ZD... JCL & VSAM 2
No new posts Generate output lines (SYSIN card for... DFSORT/ICETOOL 4
Search our Forums:

Back to Top