topic Re: How to create a forward variable ? in SAS Programming

How to create a forward variable ?

Phil_NZ — Mon, 08 Mar 2021 07:58:53 GMT

Hi SAS Users,

Normally, it is quite familiar to create a lag variable, can I ask how to create a forward variable? And is there any chance to use the option firstobs=2 to do so?

For example,

We have the dataset sashelp.class

Name	Sex	Age	Height	Weight
Alfred	M	14	69	    112.5
Alice	F	13	56.5	84
Barbara	F	13	65.3	98
Carol	F	14	62.8	102.5
Henry	M	14	63.5	102.5
James	M	12	57.3	83
Jane	F	12	59.8	84.5
Janet	F	15	62.5	112.5
Jeffrey	M	13	62.5	84
John	M	12	59	    99.5
Joyce	F	11	51.3	50.5
Judy	F	14	64.3	90
Louise	F	12	56.3	77
Mary	F	15	66.5	112
Philip	M	16	72	    150
Robert	M	12	64.8	128
Ronald	M	15	67	    133
Thomas	M	11	57.5	85
William	M	15	66.5	112

I want to create a column weight_ lead1 while weight_lead1 is forward of weight (or in another word, weight is lag of weight_lead1)

For example

Name	Sex	Age	Height	Weight   weight_lead1
Alfred	M	14	69	    112.5    84
Alice	F	13	56.5	84       98
Barbara	F	13	65.3	98       102.5
Carol	F	14	62.8	102.5    102.5
Henry	M	14	63.5	102.5    83

Could you please give me a hint or suggestion?

Warm regards.

Re: How to create a forward variable ?

ChrisNZ — Mon, 08 Mar 2021 08:02:32 GMT

You are on the right track. For example consider:

data WANT;
  merge HAVE
        HAVE (firstobs=2  /*keep= rename= options*/);
 /*no BY statement*/;
run;

Re: How to create a forward variable ?

Phil_NZ — Mon, 08 Mar 2021 08:08:12 GMT

Hi @ChrisNZ

Thank you for your suggestion, But this without BY, SAS announces error

The code:

data uuu;
set sashelp.class;
run;

data WANT;
  merge uuu
        uuu (firstobs=2 keep=weight rename=(weight=weight_lead1));
 /*no BY statement*/;
run;

And the LOG is as below

32         data WANT;
33           merge uuu
34                 uuu (firstobs=2 keep=weight rename=(weight=weight_lead1));
35          /*no BY statement*/;
36         run;

ERROR: No BY statement was specified for a MERGE statement.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.WANT may be incomplete.  When this step was stopped there were 0 observations and 6 variables.
WARNING: Data set WORK.WANT was not replaced because this step was stopped.

Warm regards.

Re: How to create a forward variable ?

andreas_lds — Mon, 08 Mar 2021 08:24:09 GMT

Use

options mergenoby=nowarn;

before the step.

Re: How to create a forward variable ?

Phil_NZ — Mon, 08 Mar 2021 08:41:31 GMT

Hi @andreas_lds

Thank you for your suggestion.

I apply this options and it run without any announcement about error.

However, from what I understand, this option just for turning off the error announcement when merge without BY.

From this document from lexjansen.

Merging with no "BY statement" is dangerous unless it is a One-to-One merging (combines the first observation
from all data sets that are named in the MERGE statement into the first observation in the new data set, the
second observation from all data sets into the second observation in the new data set, and so on)

So, I think of turning off this option right after running the code for this data step, because to me, when we assign a proc option, it will apply globally, so can you please help me to sort it out (turning off this option)?

And I also think that, apart from the ERROR notification, the results with or without this option would be the same (I checked already, but just ask for confirmation)

Warm regards.

Re: How to create a forward variable ?

yabwon — Mon, 08 Mar 2021 09:39:24 GMT

One other option would be "POINTing" next observation:

data have;
  set sashelp.class;
run;

data WANT;
  set have nobs=nobs;

  if _N_ < nobs 
    then set have(keep=weight rename=(weight=weight_lead1)) point = _N_;
    else weight_lead1 = .;
run;

Bart

Re: How to create a forward variable ?

FreelanceReinh — Mon, 08 Mar 2021 10:34:06 GMT

Or combine the two approaches and use firstobs=2 in the second SET statement:

data want;
set have end=eof;
if ~eof then set have(firstobs=2 keep=weight rename=(weight=weight_lead1));
else weight_lead1=.;
run;

Re: How to create a forward variable ?

Reeza — Mon, 08 Mar 2021 17:39:30 GMT

PROC EXPAND via SAS/ETS if you have a license for it.

Otherwise the search term you're looking for is "LEAD" and if you search lexjansen.com for LEAD variables you'll find at least a dozen papers on the topic.
https://www.lexjansen.com/search/searchresults.php?q=lead%20variable

Re: How to create a forward variable ?

Tom — Mon, 08 Mar 2021 18:23:09 GMT

MERGE without BY could work, but since that is normally a mistake you need to clearly document your code and adjust the options that flag it as an error.

* Turn off MERGENOBY option for this step ;
%let save=%sysfunc(getoption(mergenoby,keyword));
options mergenoby=nowarn;
data want;
  merge have have(firstobs=2 keep=weight rename=(weight=weight_lead)) ;
run;
* Reset MERGENOBY option ;
options &save;

Or just use two separate SET statements. But then you need control for the different number of observations. One way is to append some extra empty observations to match the number of skipped.

data want;
  set have;
  set have(firstobs=2 keep=weight rename=(weight=weight_lead)) 
      have(obs=1 drop=_all_)
  ;
run;

Now if your data is grouped you need a way to not take the value from the next group. You could use LAST. flag to check for that and then clear the value.

data want;
  set have;
  by id;
  set have(firstobs=2 keep=weight rename=(weight=weight_lead)) 
      have(obs=1 drop=_all_)
  ;
  if last.id then call missing(weight_lead);
run;

Re: How to create a forward variable ?

Tom — Mon, 08 Mar 2021 18:33:45 GMT

If you want to LEAD2, LEAD3, etc then you will need a different method to detect when the values should be cleared because you have reached the end of the group. In that case keep the ID variable also and test it.

data have;
 input id @;
 do row=1 to 5 ; input x @; output; end;
cards;
1 1 2 3 4 5
2 11 12 13 14 15
;

data want;
   set have;
   by id;
   set have(firstobs=2 keep=id x rename=(id=id1 x=x1)) have(obs=1 drop=_all_);
   set have(firstobs=3 keep=id x rename=(id=id2 x=x2)) have(obs=2 drop=_all_);
   set have(firstobs=4 keep=id x rename=(id=id3 x=x3)) have(obs=3 drop=_all_);
   if id1 ne id then call missing(x1);
   if id2 ne id then call missing(x2);
   if id3 ne id then call missing(x3);
   drop id1-id3;
run;

proc print;
run;

Obs    id    row     x    x1    x2    x3

  1     1     1      1     2     3     4
  2     1     2      2     3     4     5
  3     1     3      3     4     5     .
  4     1     4      4     5     .     .
  5     1     5      5     .     .     .
  6     2     1     11    12    13    14
  7     2     2     12    13    14    15
  8     2     3     13    14    15     .
  9     2     4     14    15     .     .
 10     2     5     15     .     .     .

Re: How to create a forward variable ?

ChrisNZ — Mon, 08 Mar 2021 20:48:36 GMT

> So, I think of turning off this option right after running the code for this data step, because to me, when we assign a proc option, it will apply globally, so can you please help me to sort it out (turning off this option)?

Your administrator decided that this option was a safer default.

In your case, you know what you want and what you are doing (one-to-one merge), so you needed to disable it before the data step, and you can re-enable it after the data step.

Another way, as shown, is to use two SET statements.

I would not use the suggestion that uses POINT= though. It is good that it is presented to you so you know about it, but know that POINT= is very slow.

Re: How to create a forward variable ?

yabwon — Mon, 08 Mar 2021 20:19:36 GMT

True, POINT= is slower, but on the other hand it allow non-sequential read, e.g you can read dataset backward without resorting it. And for small datasets (up to 1 million obs.) the difference in performance is almost intangible.

Like everywhere in programming something for something. 🙂

Bart

Re: How to create a forward variable ?

Phil_NZ — Mon, 08 Mar 2021 21:11:47 GMT

Hi @yabwon (Bart)

Thank you for your solution, Your code leads me to the very nice nobs=nobs

However, I have two concerns here in your code, could you please help me to sort it out?

1. When I run the code in my head, let's say the first iteration:

_N_=1 /* From this document _N_ variable increments by one each time the data step passes by a data statement*/

then set have() point = 1

else weight_lead1=.

I do not understand how SAS create values for data WANT here ( there is no hint for the lead condition to me), could you please explain the mechanism to me?

2. When I copy and run your code without any adjustment, the error is as below

32         data WANT;
33           set have nobs=nobs;
34         
35           if _N_ < nobs
36             then set have(keep=weight rename=(weight=weight_lead1)) point = _N_;
37             else weight_lead1 = .;
38         run;

ERROR: The POINT= data set option is not valid for the data set WORK.HAVE, the data set must be accessible by observation number 
       for POINT= processing.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.WANT may be incomplete.  When this step was stopped there were 0 observations and 6 variables.
WARNING: Data set WORK.WANT was not replaced because this step was stopped.

Could you please help me to sort it out?

Warm regards.

Re: How to create a forward variable ?

Phil_NZ — Mon, 08 Mar 2021 21:31:34 GMT

Hi @FreelanceReinh

Thank you for your suggestion. I learn more about the option end=eof. The code runs flawlessly.

But I still confuse how the data want receive the value of weight.lead1.

For example, let's say the first iteration.

data want;
/*_N_=1 for the first iteration*/
set sashelp.class end=eof;
/*end=19 (size of sashelp.class)*/
if ~eof then set sashelp.class(firstobs=2 keep=weight rename=(weight=weight_lead1));
/* if not the last observation of sashelp.class  then what ???*/
else weight_lead1=.;/*Last obs receive missing value*/
run;


data want;
/*_N_=2 for the second iteration*/
set sashelp.class end=eof;
/*end=19 (size of sashelp.class)*/
if ~eof then set sashelp.class(firstobs=2 keep=weight rename=(weight=weight_lead1));
/* if not the last observation of sashelp.class  then what ???*/
else weight_lead1=.;/*Last obs receive missing value*/
run;

I am stuck in the bold place in this code, could you please help me to sort it out?

Warm regards.

Re: How to create a forward variable ?

yabwon — Mon, 08 Mar 2021 21:43:12 GMT

About 2)

From the doc:

Restrictions	You cannot use POINT= with a BY statement, a WHERE statement, or a WHERE= data set option. In addition, you cannot use POINT= with transport format data sets, data sets in sequential format on tape or disk, and SAS/ACCESS views or the SQL procedure views that read data from external files.

Maybe your file is one of that case? Try to run proc contents on the HAVE and see the output, is the "Point to Observations" set to Yes?

Other possibility is that you have compress option on (like in this thread https://communities.sas.com/t5/SAS-Procedures/Point-error/td-p/144266).

About 1)

The nobs value is extracted in the compilation time. So you can "almost" imagine this code like (assuming that HAVE has 19 observations):

data WANT;
  set have nobs=nobs; /* FIRST set */

  if _N_ < 19 /* nobs - this value is known in compilation phase */
    then set have(keep=weight rename=(weight=weight_lead1)) point = _N_; /* SECOND set */
    else weight_lead1 = .;
run;

so the second set reading is executed only for observations 1 to 18 from the first set, and when _N_=19 the "else" is executed.

By the way, I just realised I made a mistake in the code, sorry for that. It should be as follows (the observation pointed should be _N_+1):

data have;
  set sashelp.class;
run;

data WANT;
  set have nobs=nobs;

  if _N_ < nobs then
    do;
      point = _N_+1;
      set have(keep=weight rename=(weight=weight_lead1)) point = point;
    end;
    else weight_lead1 = .;
run;

Hope it helps.

All the best

Bart

Re: How to create a forward variable ?

Phil_NZ — Mon, 08 Mar 2021 22:37:13 GMT

Hi @Reeza

It is a breath-taking solution to me!

Well ,I found a document, and after applying to my code, I get things easily.

Normally, I need to use

lagx=ifn(first.id,.,lag(x));

but proc convert can do all of things (set the first.id =. automatically), the same with lead (set the last.id=.).

There are two minor questions here:

1. Can you please tell me what do you mean about "if you have a license for it" , it is "for SAS" or for what, please?

2. When I run the code below

data uuu;
set sashelp.class;
run;
proc sort data=uuu;
by sex;
run;

proc expand data=uuu method=none;
	by Sex;
	convert weight=weight_lead1 / transformout=(lead 1);
	convert weight=weight_lead2 / transformout=(lead 2);
	convert weight=weight_lag1 / transformout=(lag 1);
run;

The result is as below

There is column name TIME coming from nowhere, can you please explain to me what it is for and why it exists in my output like that?

Warmest regard and thanks.

Re: How to create a forward variable ?

ChrisNZ — Mon, 08 Mar 2021 22:53:45 GMT

Hence why you should turn off the option before the step and turn it on again after.

Re: How to create a forward variable ?

Reeza — Mon, 08 Mar 2021 23:09:34 GMT

1. Can you please tell me what do you mean about "if you have a license for it" , it is "for SAS" or for what, please?

SAS has different modules, EXPAND is part of the ETS module. You can choose which modules to have installed or licensed. ETS is Econometrics and Time Series

SAS/OR is Operations Research for example.

Different modules include different procs. If you're familiar with R/Python, its similar to different packages or libraries for different types of analysis.

PROC EXPAND is designed for time series data. If you think about it, when using LEAD you usually need some kind of variable that represents an order or time, not just the implication of the row position. What happens if you have missing data?

You're currently using a mock example which is unrealistic in that regard. The leading row in that case has no business or logical context, whereas looking at the future or previous value for sales could be informative. If you have a use case that is similar you can either add a mock time variable to get the same functionality or it generates it.

Re: How to create a forward variable ?

Phil_NZ — Mon, 08 Mar 2021 23:19:58 GMT

Hi @ChrisNZ

It is just because I turn off the notice by using

options mernoby=nowarn;

to compress the error notification and then just recognize that this options stuff will apply globally in my SAS EG ,so I try to turn the notice on again by

options mernoby=warn;

Simplistically speaking, it is a newbie's mistake.

Warm regards.

Re: How to create a forward variable ?

FreelanceReinh — Mon, 08 Mar 2021 23:32:20 GMT

@Phil_NZ wrote:

Hi @FreelanceReinh

Thank you for your suggestion. I learn more about the option end=eof. The code runs flawlessly.

But I still confuse how the data want receive the value of weight.lead1.

You can think of the two SET statements as reading from two separate datasets:

sashelp.class (19 obs.)
a dataset consisting of only the weight values of all but the first observation of sashelp.class (i.e. 18 obs.), with weight_lead1 as the name of that single variable.

In the first iteration of the DATA step -- _N_=1, eof=0, hence ~eof=1 -- the first SET statement reads the first observation of the first dataset and the second SET statement reads the first observation of the second dataset (which contains the weight value of the second observation of sashelp.class in variable weight_lead1). So, at the end of the first iteration the program data vector contains the values of the first observation of sashelp.class and in variable weight_lead1 the desired "lead" value of variable weight.

In the second iteration of the DATA step -- _N_=2, still eof=0, hence ~eof=1 -- the first SET statement reads the second observation of the first dataset and the second SET statement reads the second observation of the second dataset (which contains the weight value of the third observation of sashelp.class in variable weight_lead1). So, at the end of the second iteration the program data vector contains the values of the second observation of sashelp.class and, again, in variable weight_lead1 the desired "lead" value of variable weight.

... and so on until the 18th observation.

In the 19th iteration of the DATA step -- _N_=19, at the beginning still eof=0 -- the first SET statement reads the last (=19th) observation of the first dataset, the value of eof is switched to 1, hence ~eof=0, and therefore the second SET statement is not executed, which is good because reading past the end of the dataset (remember that the second dataset has only 18 observations, the last of which has already been read in the previous iteration of the DATA step) would cause the DATA step to terminate immediately. That is, dataset WANT would have only 18 observations (the observation with name='William' would be missing). Now, with ~eof being false (0), the ELSE branch is executed instead and overwrites the retained value of weight_lead1 (=112, from the last observation of the second dataset -- remember that variables read by a SET statement are automatically retained) with a missing value.

In the last (=20th!) iteration of the DATA step -- _N_=20, eof=1 -- the first SET statement reads past the end of the first dataset and this terminates the DATA step. So, the 19th observation of dataset WANT (written at the end of the 19th iteration) is also the last.