Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

SUM of values in SAS

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> All Other Mainframe Topics
View previous topic :: :: View next topic  
Author Message
rikdeb

New User


Joined: 19 Jan 2009
Posts: 59
Location: hyderabad

PostPosted: Wed Dec 10, 2014 2:37 am    Post subject: SUM of values in SAS
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: 7913
Location: Bellevue, IA

PostPosted: Wed Dec 10, 2014 4:22 am    Post subject:
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: 59
Location: hyderabad

PostPosted: Wed Dec 10, 2014 8:53 am    Post subject:
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: 8593
Location: Back in jolly old England

PostPosted: Wed Dec 10, 2014 3:19 pm    Post subject:
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: 59
Location: hyderabad

PostPosted: Wed Dec 10, 2014 4:01 pm    Post subject:
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: 7913
Location: Bellevue, IA

PostPosted: Wed Dec 10, 2014 5:15 pm    Post subject:
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: 8593
Location: Back in jolly old England

PostPosted: Wed Dec 10, 2014 5:23 pm    Post subject:
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: 7913
Location: Bellevue, IA

PostPosted: Thu Dec 11, 2014 5:58 am    Post subject:
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: 59
Location: hyderabad

PostPosted: Thu Dec 11, 2014 8:36 am    Post subject:
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    IBMMAINFRAMES.com Support Forums -> All Other Mainframe Topics All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Overlaying one set of charater values... Kevin Lindsley SYNCSORT 7 Sat Nov 05, 2016 3:21 am
No new posts Low values Results from VARCHAR FORMAT balaji81_k DB2 10 Thu Oct 20, 2016 1:18 am
No new posts How can we create a flat file in JAVA... rakesh.v18 Java & MQSeries 7 Fri Sep 23, 2016 10:46 pm
No new posts Using 'parm' to vary SORTOUT record v... Sysaron DFSORT/ICETOOL 13 Wed Sep 07, 2016 9:24 pm
No new posts Unstring list of values into an array. Roshnii COBOL Programming 8 Mon Jun 27, 2016 6:25 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us