BookmarkSubscribeRSS Feed
ZRick
Obsidian | Level 7

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;

6 REPLIES 6
Ksharp
Super User

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;

ZRick
Obsidian | Level 7

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

Howles
Quartz | Level 8

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?

ZRick
Obsidian | Level 7

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?

Ksharp
Super User

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

Keith
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1874 views
  • 0 likes
  • 4 in conversation