topic Re: %SYSRC in SAS Programming

%SYSRC

HeatherNewton — Wed, 03 Aug 2022 09:06:11 GMT

data sdata.latest_customer:
    set_curr_customer;
    do until(_IORC_=%SYSRC(_DSENOM));
        modify sdata.latest_customer key=i_customer_no;
        select(_IORC_);
            when (%SYSRC(_SOK)) do;
                if eff_to_date=. then do;
                    eff_to_date=t_eff_to_date;
                end;
                replace sdata.latest_customer;
            end;
            when (%SYSRC (_DSENOM)) DO;
                _error_=0;
            end;
        otherwise;
       end;
    end;
run;

data wdata.customer_h;
set wdata.customer_h (where=(eff_to_date ~= .)) 
        sdata.latest (drop=t_eff_to_date);
run;

Hi I have trouble understanding the above code, could you please assist. thanks. I did try to review in sas documentation about the variable %SYSRC but got nowhere..

Thanks for your help.

Re: %SYSRC

PeterClemmensen — Wed, 03 Aug 2022 11:07:23 GMT

This code updates / modifies values in curr_customer with values from the indexed data set sdata.latest_customer. I assume that the line

set_curr_customer;

should really be

set curr_customer;

The value of _IORC_ is a numeric return code that indicates the status of the I/O operation from the most recently executed MODIFY or SET statement with KEY=. The values of the _IORC_ automatic variable are internal and subject to change. Therefore it makes sense not to check the actual numeric value, but instead check the Mnemonic Value of the variables. That is what we use %SYSRC for.

There are a few good recourses and examples in the Modify Statement Doc and the Error Checking When Using Indexes to Randomly Access or Update Data link.

Re: %SYSRC

Tom — Wed, 03 Aug 2022 13:07:53 GMT

%SYSRC() is not a VARIABLE. It is a SAS defined autocall macro.

To see what it does either look at the source code, or just test the usage in your example:

228  %put %SYSRC(_SOK);
0
229  %put %SYSRC(_DSENOM);
1230015

So zero is "_SOK", perhaps the OK in the name means everything is okay?

And 1,230,015 is "_DSENOM" which I had to look at the source to decode into english.

Re: %SYSRC

HeatherNewton — Wed, 03 Aug 2022 13:19:07 GMT

i find that _SOK means the function is successful

actually what is i_customer_no? do I have to let it to some customer no in dataset _curr_customer?

Re: %SYSRC

HeatherNewton — Wed, 03 Aug 2022 13:38:35 GMT

in the line

 when (%SYSRC(_SOK)) do;

is %sysrc(_SOK) boolean? if true, do what follows?

Re: %SYSRC

Tom — Wed, 03 Aug 2022 13:47:56 GMT

@HeatherNewton wrote:

in the line
 when (%SYSRC(_SOK)) do;
is %sysrc(_SOK) boolean? if true, do what follows?

No.

That is part of the SELECT/WHEN/END block. Since the SELECT part included an expression the value in the WHEN () part is compared to the value in the SELECT () part. So

select(_IORC_);
  when (%SYSRC(_SOK)) do;
    if eff_to_date=. then do;
      eff_to_date=t_eff_to_date;
    end;
    replace sdata.latest_customer;
  end;
  when (%SYSRC (_DSENOM)) DO;
    _error_=0;
  end;
  otherwise;
end;

it is same as

if _IORC_=%SYSRC(_SOK) then do;
  if eff_to_date=. then do;
    eff_to_date=t_eff_to_date;
  end;
  replace sdata.latest_customer;
end;
else if _IORC_= %SYSRC(_DSENOM) then DO;
  _error_=0;
end;
else ;

Re: %SYSRC

HeatherNewton — Wed, 03 Aug 2022 14:33:11 GMT

when is _IORC_=%SYSRC(SOK) and when is _IORC_=%SYSRC(_DSENOM)?

Re: %SYSRC

Tom — Wed, 03 Aug 2022 14:40:46 GMT

Google helps.

https://www.google.com/search?q=%40sas.com+_iorc_

The first hit is actually the old version 8 documentation, but since this functionality has not changed and those pages are a LOT easier to read than the new help pages why don't you start there.

https://v8doc.sas.com/sashtml/lrcon/z1104667.htm

Re: %SYSRC

HeatherNewton — Wed, 03 Aug 2022 21:02:53 GMT

So does it mean

We compare each row in _curr_customer with each row in sdata.latest_customer by i_customer_no.
If i_customer_no matches it means _iorc_=%SYSRC(_SOK) we do:

if eff_to_date=. then do;

eff_to_date=t_eff_to_date;

we replace the row in sdata.latest_customer with new values
If i_customer_id not match, assign _error_=0

here I don’t get the line ‘do until means…’ I thought it ends when each observation in _curr_customer already compare with sdata.latest_customer…？

Re: %SYSRC

Tom — Wed, 03 Aug 2022 21:43:59 GMT

So that is different than the technical question of what %SYSRC() does.

Look at the whole data step.

First thing you will notice it will end the way most data steps end, when it reads past the end of the input.

data sdata.latest_customer:
    set curr_customer;

In this case when the SET statement runs out of data to read from curr_customer.

Now let's look at the DO loop.

    do until(_IORC_=%SYSRC(_DSENOM));

Since it is a DO UNTIL() loop the DO loop will run at least once for every observation read from curr_customer.

It then tries to use the MODIFY statement with the KEY= option.

        modify sdata.latest_customer key=i_customer_no;

So that is going to do a few things. One is lets the data step know that the dataset listed in the DATA statement is not being built brand new, but it is just going to be modified in-place. Second is that it will search for the observation to point to based on the index named in the KEY= option. So whatever value for the variables that are used to make the index were read from CURR_CUSTOMER is going to be used to find the record in SDATA.LATEST_CUSTOMER. And finally it is going to set the _IORC_ variable (and also the _ERROR_ variable) to indicate if the lookup in the index succeeded or not.

Now the SELECT block is going to decide what to do based on the _IORC_ value set by the MODIFY KEY= statement.

When the record is FOUND ( the value assigned to the _SOK pneumonic ) then it potentially updates the eff_to_date variable based on the value or the t_eff_to_date variable read from CURR_CUSTOMER.

And when the value is not found ( the value assigned to the _DSENOM pneumonic you asked about before) then instead it just resets the _ERROR_ flag to FALSE. That will prevent the data step from crashing as soon it one of the values read from CURR_COSTEMER is not found.

Now the UNTIL() clause is tested. So it didn't find a record the DO loop ends. If it did (or it got some other error) then the loop continues.

The purpose of the DO UNTIL() loop is to let you update multiple observations in sdata.latest_customer from a single observation read from CURR_CUSTOMER. For that to actually happen the INDEX needs to be defined without the UNIQUE option.

Here is an example that uses the SASHELP.CLASS dataset and AGE as the key variable for the INDEX.

data class (index=(age));
  set sashelp.class;
run;

data class ;
  input age new_name $ ;
  put _n_= age= new_name= _iorc_= ;
  do loop=1 by 1 until (_iorc_=%sysrc(_dsenom)) ;
    modify class  key=age ;
    put _n_= loop= _iorc_= name= sex=;
    select(_IORC_);
      when (%SYSRC(_SOK)) do;
        name=new_name;
        replace class;
      end;
      when (%SYSRC(_DSENOM)) DO;
        _error_=0;
      end;
      otherwise;
    end;
  end;
cards;
14 Zebra 
99 Giraffe 
; 

proc print data=class;
run;

So you can see that the one transaction record with AGE=14 changed the name on multiple observations in the WORK.CLASS dataset since the DO loop ran multiple times. But the AGE=99 transaction is ignored since it did not match anything in the WORK.CLASS dataset.

Re: %SYSRC

HeatherNewton — Thu, 04 Aug 2022 02:45:43 GMT

actually just before the code I posted I notice a command to create index but I am not able to google it or find it on sas documentation the exact function. Is it correct to guess that it is building an composite index with two var org_code, cust_no for fast execution in searching. I.e. if the first line of _curr_customer has org_code=ABC and cust_no=0001, using the index, program will go directly to the observations in sdata.latest_customer where org_code=ABC and cust_no=0001 and no need to examine every observation in sdata.latest_customer and hence save time...

%ADD_IDX(SDATA.LATEST_CUSTOMER, I_CUSTOMER_NO,%STR(org_code, cust_no));

data sdata.latest_customer:
    set_curr_customer;
    do until(_IORC_=%SYSRC(_DSENOM));
        modify sdata.latest_customer key=i_customer_no;
        select(_IORC_);
            when (%SYSRC(_SOK)) do;
                if eff_to_date=. then do;
                    eff_to_date=t_eff_to_date;
                end;
                replace sdata.latest_customer;
            end;
            when (%SYSRC (_DSENOM)) DO;
                _error_=0;
            end;
        otherwise;
       end;
    end;
run;

data wdata.customer_h;
set wdata.customer_h (where=(eff_to_date ~= .)) 
        sdata.latest (drop=t_eff_to_date);
run;

Re: %SYSRC

Tom — Thu, 04 Aug 2022 03:11:37 GMT

That is a macro call. Look at the source code of the macro to understand what it does.

Or run it with the MPRINT option turned on to see in the SAS LOG the SAS code that the macro generates.

I would assume that it is building an index named I_CUSTOMER_NO on the dataset named SDATA.LATEST_CUSTOMER that uses the two variables org_code and cust_no as the key variables.

Re: %SYSRC

HeatherNewton — Thu, 04 Aug 2022 04:37:33 GMT

eff_to_date exist in both sdata.latest_customer and _curr_customer

t.eff_to_date exist in only _curr_customer

for the line:

if eff_to_date=. then do;

eff_to_date=t_eff_to_date;

are we assigning _curr_customer's t.eff_to_date to sdata.latest_customer's eff_to_date

or are we assigning _curr_customer's t.eff_to_date to _curr_customer's eff_to_date

then follow by replacing the whole observation (with all other columns not mention here) in sdata.latest_customer?

Re: %SYSRC

Tom — Thu, 04 Aug 2022 12:39:28 GMT

@HeatherNewton wrote:

eff_to_date exist in both sdata.latest_customer and _curr_customer

t.eff_to_date exist in only _curr_customer

for the line:

if eff_to_date=. then do;

eff_to_date=t_eff_to_date;

are we assigning _curr_customer's t.eff_to_date to sdata.latest_customer's eff_to_date

or are we assigning _curr_customer's t.eff_to_date to _curr_customer's eff_to_date

then follow by replacing the whole observation (with all other columns not mention here) in sdata.latest_customer?

When that IF statement runs a record in sdata.latest_customer was found. So the value of EFF_TO_DATE is the value read from there. So this code is only replacing missing values of EFF_TO_DATE with the value of T_EFF_TO_DATE. If EFF_TO_DATE in sdata.latest_customer was not missing then it will be unchanged. Any value of EFF_TO_DATE read from CURR_CUSTOMER is ignored (unless it has one of the variables used by the INDEX).

Re: %SYSRC

HeatherNewton — Thu, 04 Aug 2022 23:43:38 GMT

I understand for observation in sdata.latest_customer where eff_to_date is null, we replace null with value of t_eff_to_date. How about the rests of columns in the same observation, do they get replace with value from observation from _curr_customer? I reviewed the saw documentation about REPLACE, it seems indicating that the whole observation is replaced…

I also hear this is related to slow changing dimension, I am not familiar with this terminology, what is it significance here?

Re: %SYSRC

Tom — Fri, 05 Aug 2022 02:38:35 GMT

Slow changing dimension is computer speak for something that changes over time, but not very much. Google it.

In the code you posted the only variable from CURR_CUSTOMER that matter are the ones that are used by the INDEX referenced in the KEY= option of the MODIFY statement and the one used to replace the value of eff_to_date. The reason for this is that when the MODIFY statement finds a match it reads in the observation. So all of the variables that are in sdata.latest_customer will have any values that might have been there from CURR_CUSTOMERS replaced by the values just read in from sdata.latest_customer. So the IF statement is the only statement before the REPLACE statement that could make any changes to anything before it gets re-written to sdata.latest_customer.