DATA Step, Macro, Functions and more

how to write an accumulation statement so that all the obs in the transaction ds are added to the master ds?

Reply
Frequent Contributor
Posts: 133

how to write an accumulation statement so that all the obs in the transaction ds are added to the master ds?

On advanced certification book, it says to update record using transaction dataset and by method.

book suggests we write an accumulation statement so that all the obs inthe transaction ds are added to the master observation? I am using following example.


Following code trans dataset, 13 is a dup, 2nd 13 was writen to mas data set, which is not right.

data mas;

input id var1 $;

cards;

12 fish

13 pole

14 system

15 new

;

data trans;

input id var1 $;

cards;

13 ab

13 cd

14 book

;

data mas;

modify mas trans;

by id;

run;

proc print data=mas;run;

Super User
Posts: 10,041

how to write an accumulation statement so that all the obs in the transaction ds are added to the master ds?

Is it what you need?

data mas;
input id var1 $;
cards;
12 fish
13 pole
14 system
15 new
;

 

data trans;
input id var1 $;
cards;
13 ab
13 cd
14 book
;

 

data mas; 
modify mas trans ;
by id;
select(_iorc_); 
  when(%sysrc(_sok))  output;
  when(%sysrc(_dsenom)) do;
                     _error_ = 0;
                    output;
                        end;
  otherwise ;
end;
run;

Frequent Contributor
Posts: 133

how to write an accumulation statement so that all the obs in the transaction ds are added to the master ds?

Do you know exactly what "an accumulation statement" is?

Regular Contributor
Posts: 184

how to write an accumulation statement so that all the obs in the transaction ds are added to the master ds?

My guess is that the problem is one of arithmetic accumulation. The UPDATE and MODIFY statements can handle this, but it gets tricky if the variable to be incremented in the master data set and the increments in the transaction data set have the same name. A RENAME is needed.

Example:

data master ;

input ID HowMuch ;

cards ;

98   10

99 1000

;

data transaction ;

input ID HowMuch ;

cards ;

98    2

99  200

99   30

99    4

;

data   master ;

modify master

       transaction( rename = HowMuch=increment ) ;

by ID ;

HowMuch + increment ;

run ;

ZRick wrote:

Do you know exactly what "an accumulation statement" is?

Frequent Contributor
Posts: 133

how to write an accumulation statement so that all the obs in the transaction ds are added to the master ds?

I have couple of questions regarding your code:

_dsenom is for updating records using index, should it be _DSEMTR?

I don't understand the purpose of  "otherwise ;"

Also, why do we have two output statments?

Super User
Posts: 10,041

how to write an accumulation statement so that all the obs in the transaction ds are added to the master ds?

My code is very simple and not reliable .

If you want more detail. Check it out at SAS document( SAS/BASE dictionary) ,search MODIFY statement,

you will find lots of examples and explanation.

Ksharp

Regular Contributor
Posts: 151

how to write an accumulation statement so that all the obs in the transaction ds are added to the master ds?

An accumulation statement is used to update values in a master dataset from a transaction dataset (e.g. stock number) where duplicate id's exist.  In your example there isn't such a numeric field and therefore an accumulation statement can't be used.

Below is a link to the SAS documentation on using the MODIFY statement, take a look at example 5 which demonstrates using an accumulation statement to deal with duplicate values.

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000173361.htm

Ask a Question
Discussion stats
  • 6 replies
  • 748 views
  • 0 likes
  • 4 in conversation