BookmarkSubscribeRSS Feed
HeatherNewton
Quartz | Level 8

 

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.

15 REPLIES 15
PeterClemmensen
Tourmaline | Level 20

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.

 

Tom
Super User Tom
Super User

%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.

Tom_0-1659532061121.png

 

HeatherNewton
Quartz | Level 8

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?

HeatherNewton
Quartz | Level 8

in the line

 when (%SYSRC(_SOK)) do;

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

 

Tom
Super User Tom
Super User

@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 ;

 

HeatherNewton
Quartz | Level 8

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

 

Tom
Super User Tom
Super User

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

 

HeatherNewton
Quartz | Level 8

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…?

 

 

Tom
Super User Tom
Super User

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.

HeatherNewton
Quartz | Level 8

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;
Tom
Super User Tom
Super User

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. 

 

HeatherNewton
Quartz | Level 8

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?

Tom
Super User Tom
Super User

@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).

HeatherNewton
Quartz | Level 8

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?

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 15 replies
  • 4185 views
  • 0 likes
  • 3 in conversation