returning a variable's value in a given month

[ubshams]

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!

[PeterClemmensen]

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.

[ubshams]

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

[PeterClemmensen]

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

[ubshams]

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.

[PeterClemmensen]

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;

[Tom]

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;

[Shmuel]

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;

[Tom]

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.

[mkeintz]

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