View previous topic :: View next topic
|
Author |
Message |
rikdeb
New User
Joined: 19 Jan 2009 Posts: 63 Location: hyderabad
|
|
|
|
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 |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008 Posts: 8696 Location: Dubuque, Iowa, USA
|
|
|
|
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 |
|
|
rikdeb
New User
Joined: 19 Jan 2009 Posts: 63 Location: hyderabad
|
|
|
|
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 |
|
|
expat
Global Moderator
Joined: 14 Mar 2007 Posts: 8797 Location: Welsh Wales
|
|
|
|
As Robert has said you need to change LAST.CODE to LAST.IDNO |
|
Back to top |
|
|
rikdeb
New User
Joined: 19 Jan 2009 Posts: 63 Location: hyderabad
|
|
|
|
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 |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008 Posts: 8696 Location: Dubuque, Iowa, USA
|
|
|
|
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 |
|
|
expat
Global Moderator
Joined: 14 Mar 2007 Posts: 8797 Location: Welsh Wales
|
|
|
|
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 |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008 Posts: 8696 Location: Dubuque, Iowa, USA
|
|
|
|
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 |
|
|
rikdeb
New User
Joined: 19 Jan 2009 Posts: 63 Location: hyderabad
|
|
|
|
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 |
|
|
|