topic Re: how to do that in SAS Enterprise Guide

Assign a value based on date / working day

alepage — Thu, 15 Dec 2022 21:34:34 GMT

Hello,

Here's my dataset.

I would like to develop some go that will permit to map the date2 at the good value as it is into the dataset, based on date1 and workingday value. When it is a working day, workingday eq one and if not it equals zero.

So I would like a sas code example to map date2 base on date1 and workday value.

Does someone know how to do that ?

Regards,

data want;
infile datalines delimiter=','; 
input date1: yymmdd10. workingday: 1. date2: yymmdd10.;
format date1 yymmdd10. workingday 1. date2 yymmdd10.;
datalines;
2020-01-01,0,20200103
2020-01-02,0,20200103
2020-01-03,1,20200103
2020-01-04,0,20200106
2020-01-05,0,20200106
2020-01-06,1,20200106
2020-01-07,1,20200107
2020-01-08,1,20200108
2020-01-09,1,20200109
2020-01-10,1,20200110
2020-01-11,0,20200113
2020-01-12,0,20200113
2020-01-13,1,20200113
;
run;

Please note that I would like to have date2 using yymmddn8. format but I have tried it the code and it does not like it.

Re: how to do that

PeterClemmensen — Thu, 15 Dec 2022 20:57:17 GMT

How about

data have;
infile datalines delimiter=','; 
input date1: yymmdd10. workingday: 1.;
format date1 yymmdd10. workingday 1.;
datalines;
2020-01-01,0 
2020-01-02,0 
2020-01-03,1 
2020-01-04,0 
2020-01-05,0 
2020-01-06,1 
2020-01-07,1 
2020-01-08,1 
2020-01-09,1 
2020-01-10,1 
2020-01-11,0 
2020-01-12,0 
2020-01-13,1 
;
run;

data want(drop = d);
   do until (workingday);
      set have;
   end;

   d = date1;

   do until (workingday);
      set have;
      date2 = d;
      output;
   end;

   format date2 yymmddn8.;
run;

Re: how to do that

ballardw — Thu, 15 Dec 2022 21:01:18 GMT

What are the rules for "map the date2 at the good value"? I do not see anything that I can follow as to what value is used where.

You should provide an example of the data after the mapping is done.

What do you mean by "does not like it" in "Please note that I would like to have date2 using yymmddn8. format but I have tried it the code and it does not like it."

This runs just fine and displays the values of date2 that way:

data want;
infile datalines delimiter=','; 
input date1: yymmdd10. workingday: 1. date2: yymmdd10.;
format date1 yymmdd10. workingday 1. date2 yymmddn8.;
datalines;
2020-01-01,0,20200103
2020-01-02,0,20200103
2020-01-03,1,20200103
2020-01-04,0,20200106
2020-01-05,0,20200106
2020-01-06,1,20200106
2020-01-07,1,20200107
2020-01-08,1,20200108
2020-01-09,1,20200109
2020-01-10,1,20200110
2020-01-11,0,20200113
2020-01-12,0,20200113
2020-01-13,1,20200113
;

There is no INFORMAT of yymmddn8. though.

Re: how to do that

Tom — Thu, 15 Dec 2022 21:03:34 GMT

So assuming that what you are asking is how to set DATE2 to the NEXT value of DATE1 that is a WORKINGDAY, then the easiest way is to re-order the data in DESCREASING order of DATE1 and just RETAIN the value. (Note that for RETAIN to work correctly the variable cannot be on the input dataset, otherwise each time the SET statement executes the retained value is overwritten by the value read in.)

data have;
  input date1 :yymmdd10. workingday ;
  format date1 yymmdd10.;
datalines;
2020-01-01 0
2020-01-02 0
2020-01-03 1
2020-01-04 0
2020-01-05 0
2020-01-06 1
2020-01-07 1
2020-01-08 1
2020-01-09 1
2020-01-10 1
2020-01-11 0
2020-01-12 0
2020-01-13 1
;

proc sort data=have;
  by descending date1;
run;

data want;
  set have;
  by descending date1 ;
  retain date2;
  format date2 yymmdd10.;
  if workingday then date2=date1;
run;

proc print;
run;

Re: how to do that

alepage — Thu, 15 Dec 2022 21:37:15 GMT

It seems so simple for you and for me I had no idea how to do that. Sorting by descending order and using a retain statement. It does exactly what I wish.

Thank you very much