topic Re: returning a variable's value in a given month in SAS Programming

returning a variable's value in a given month

ubshams — Tue, 17 Sep 2019 13:30:21 GMT

Hi Everyone,

I have a numeic timeseries variable "x" from 20701-04 to 2018-12. Lets say x =5 in 201812. I would like to create a variable "y" based just the 2018-12 value of x which is 5. So y needs to be equal to 5 at all months. How do I do this? I looked into the put() function but was confused on how to use it for this. The months are in yymmd. format.

Thanks!

Re: returning a variable's value in a given month

PeterClemmensen — Tue, 17 Sep 2019 13:33:48 GMT

When you say 20701-04, what date does that represent?

Also, are you able to post some example of how your data is structured and what your desired result looks like? Makes it easier to provide a usable code answer.

Re: returning a variable's value in a given month

ubshams — Tue, 17 Sep 2019 13:41:59 GMT

Sorry, typo, I meant 2007-01, representing Jan 2007. And 2018-12 represents Dec 2018.

Re: returning a variable's value in a given month

PeterClemmensen — Tue, 17 Sep 2019 13:43:19 GMT

Can you post some sample data so we can see how your data is structured?

Re: returning a variable's value in a given month

ubshams — Tue, 17 Sep 2019 13:55:54 GMT

Sure:

EOM x c y

2007-01 1   2
2007-02 3   3
...
2018-12 5 1

y needs to equal to 5 multiplied by a given value of c in a month.

so y in 2007-01 need to be 5*2 and y in 201812 need to be 5*1, etc.

Thanks.

Re: returning a variable's value in a given month

PeterClemmensen — Tue, 17 Sep 2019 13:59:44 GMT

I just made up some simple data. You can do something like this

data have;
    do dt='30apr2017'd by 0 while (dt le '31dec2018'd);
        x=ifn(dt='31dec2018'd, 5, 1);
        c=2;
        output;
        dt=intnx('month', dt, 1, 'e');
    end;
    format dt yymmd.;
run;

data want;
    do until (lr1);
        set have end=lr1;
        if dt='31dec2018'd then _x=x;
    end;
    do until(lr2);
        set have end=lr2;
        y=_x*c;
        output;
    end;
run;

Re: returning a variable's value in a given month

Tom — Tue, 17 Sep 2019 14:07:50 GMT

There isn't any other grouping variable?

data want;
  if _n_=1 and not eof then do;
     set have (where=(EOM='2018-12')) end=eof;
     dec2018_x = x;
     retain dec2018_x;
  end;
  set have;
  y=c*dec2018_x;
run;

Re: returning a variable's value in a given month

Shmuel — Tue, 17 Sep 2019 14:13:11 GMT

The logic should be:

1) Read the dataset, locate the obs with EOM=2018-12 and save the value of X int a macro variable.

Consider two situations:

- what if there is no such obs ?

- what if there are more than one such obs but with different X values ?
assuming there is only one such obs continue to step 2.

2) Retain the value of X found and compute C for each obs.

The code should like:

Data _null_;
 set have (where=(put(EOM,yymmdd5.)='2018-12');
      call symput('X', x);
run;

data want;
 set have;
       retain mult &x;
       y = mult *c;
      drop mult;
run;

Re: returning a variable's value in a given month

Tom — Tue, 17 Sep 2019 14:27:38 GMT

Note that putting floating point numbers into macro variables can result in unintended rounding of the values. In general it is best to keep the values in actual variables instead of storing it as text in a macro variable.

Re: returning a variable's value in a given month

mkeintz — Tue, 17 Sep 2019 20:46:29 GMT

Here's another way to retain the value of X (renamed to MULT), without running a separate data step, or issuing a RETAIN statement.

data want (drop=mult);
  if _n_=1 then set have (where=(put(EOM,yymmdd5.)='2018-12' rename=(x=mult));
  set have;
  y=mult*c;
run;

The "trick" here is to use a conditional SET statement. All variables in SET statements are automatically retained. I repeat "All variables in SET statements are automatically retained.". But typically they are populated with new values when the normal iteration of the data step re-invokes the same SET statement (or any other SET that obtains the variables in question).

But here, the "IF _N_=1" set statement reads a variable named MULT. Since that SET is never executed again, the MULT variable is neither set to missing or overwritten with a new value, and is therefore available for use in all the subsequent observations.

As in @Shmuel's suggested code, this program assume exactly one obs for 2018-12.

regards,

Mark