topic Data Step with Modifiy Statement in SAS Programming

Data Step with Modifiy Statement

FK1 — Fri, 08 Apr 2022 12:57:49 GMT

Hello.

This Knowledge Base entry shows examples for using the "modify" statement:

https://support.sas.com/kb/24/678.html

Regarding example 1, how do I have to change the SAS code in order to get this result (master) dataset:

One the one hand, I want to update existing rows in the master dataset (partno="K89R") but also on the other hand, i want to add rows, that only exist in the transaction dataset (partno="M123").

This is the code, I have so far, making use of the SYSRC Autocall Macro :


data stock;
	modify stock trans;
	by partno;

	select(_IORC_);
		when (%sysrc(_SOK))
			do;
				replace stock;
			end;

		when (%sysrc(_DSENMR))
			do;
				/*what kind of code must be here in order to add nonexisting observations in masterdataaset?*/
			end;

		otherwise
			do;
				put 'ERROR: Unexpected value for _IORC_= ' _iorc_;
				put 'Program terminating. Data step iteration # ' _n_;
				put _all_;
				stop;
			end;
	 end;
run;

Also my qustion is: how would I have to modify the code if I wanted to update not just one column but several columns?

Any help would be greatly appreciated,

FK1

Re: Data Step with Modifiy Statement

PaigeMiller — Fri, 08 Apr 2022 13:05:29 GMT

In order for anyone to write code to convert two input data sets into one output data set, we need to have the two input data sets. Please provide these two input data sets (or a small portion of each) as SAS data step code, which you can type in yourself, or you can use these instructions to create the code.

The MODIFY statement doesn't apply to DATA steps.

Re: Data Step with Modifiy Statement

Tom — Fri, 08 Apr 2022 13:15:59 GMT

You are using the REPLACE statement to update an existing observation.

Use the OUTPUT statement to write a new observation.

Re: Data Step with Modifiy Statement

FK1 — Fri, 08 Apr 2022 13:22:57 GMT

@PaigeMiller : the creation of the two datasets is also in the knowled base entry, which i posted:

/* Create STOCK data set */

data stock;
  input partno $ desc $ instock price;
datalines;
K89R seal 34 245.00
M4J7 sander 98 45.88
LK43 filter 121 10.99
MN21 brace 43 27.87
;

data trans;
  input partno $ desc $ instock price;
datalines;
K89R seal 34 300
M123 sander 98 45.88
;

Re: Data Step with Modifiy Statement

PaigeMiller — Fri, 08 Apr 2022 13:34:22 GMT

This would be done using the DATA step UPDATE statement.

proc sort data=stock; by partno; run;
proc sort data=trans; by partno; run;
data want;
    update stock trans;
    by partno;
run;

Re: Data Step with Modifiy Statement

FK1 — Fri, 08 Apr 2022 13:52:32 GMT

Thanks, @Tom : this works:


data stock;
	modify stock trans;
	by partno;

	select(_IORC_);
		when (%sysrc(_SOK))
			do;
				replace stock;
			end;

		when (%sysrc(_DSENMR))
			do;
				output;
				_error_=0;
			end;

		otherwise
			do;
				put 'ERROR: Unexpected value for _IORC_= ' _iorc_;
				put 'Program terminating. Data step iteration # ' _n_;
				put _all_;
				stop;
			end;
	 end;
run;

Re: Data Step with Modifiy Statement

Tom — Fri, 08 Apr 2022 13:55:25 GMT

There is a difference between the UPDATE statement and what can be done with the MODIFY statement.

The main differences are:

The data step using an UPDATE statement will write a whole NEW output dataset. With MODIFY you can modify can existing dataset. So if the source dataset is large and there might be a large difference in performance and disk space requirements.

The UPDATE statement will ignore missing values in the transaction dataset, leaving the existing value unchanged If you use MODIFY to perform transactions you would need to add your own logic if you want that.