Solved: Dealing with Character Variable with matched dates

Saba1 · Posted 10-30-2018 12:01 AM

I have a dataset where the first column is based on unique IDs and the second column contains regular monthly dates from 01-31-2016 till 12-31-2017 for each unique ID. Then there are several columns containing some dates (in brackets) and names of managers associated with these dates. I have around 90 columns for Managers and 3500 unique IDs. Below is the sample dataset:

Data Have;
infile datalines
dlm=","
missover
DSD;
input ID : $10.
	Date_Month : mmddyy10.
	Manager1 : $60.
	Manager2 : $60.
	Manager3 : $60. ;
format Date_Month mmddyy10. ;
datalines;
AB00046,01-31-2016,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,02-28-2016,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,03-31-2016,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,04-30-2016,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,05-31-2016,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,06-30-2016,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,07-31-2016,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,08-31-2016,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,09-30-2016,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,10-31-2016,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,11-30-2016,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,12-31-2016,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,01-31-2017,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,02-28-2017,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,03-31-2017,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,04-30-2017,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,05-31-2017,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,06-30-2017,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,07-31-2017,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,08-31-2017,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,09-30-2017,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,10-31-2017,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,11-30-2017,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,12-31-2017,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00050,01-31-2016,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,02-28-2016,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,03-31-2016,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,04-30-2016,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,05-31-2016,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,06-30-2016,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,07-31-2016,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,08-31-2016,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,09-30-2016,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,10-31-2016,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,11-30-2016,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,12-31-2016,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,01-31-2017,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,02-28-2017,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,03-31-2017,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,04-30-2017,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,05-31-2017,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,06-30-2017,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,07-31-2017,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,08-31-2017,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,09-30-2017,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,10-31-2017,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,11-30-2017,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,12-31-2017,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
;
run;

I want the following:

1- In all the columns where bracket dates' "year" and "month" match with the "year" and "month" of Date_month column, keep the names of managers. The bracket of dates should not be displayed in "want" dataset.

2- If the starting date in the bracket is earlier than the starting date of Date_month column i.e. 01-31-2016, then manager's name should start from 01-31-2016, all previous dates in the bracket must be ignored.

3- If the ending date in the bracket is either greater than the ending date of Date_month column i.e. 12-31-2017 or is not given and shown as dashes e.g. [2016-01-23 -- ], then manager name must be continued till 12-31-2017.

4- When month and year of these two dates are not matched, such manager's name must be ignored.

Please see below the required output dataset:

Data Want;
infile datalines
dlm=","
missover
DSD;
input ID : $10.
	Date_Month : mmddyy10.
	Manager1 : $60.
	Manager2 : $60.
	Manager3 : $60. ;
format Date_Month mmddyy10. ;
datalines;
AB00046,01-31-2016,Ronald Baron,Bill F. Baron,
AB00046,02-28-2016,Ronald Baron,Bill F. Baron,
AB00046,03-31-2016,Ronald Baron,Bill F. Baron,
AB00046,04-30-2016,Ronald Baron,Bill F. Baron,
AB00046,05-31-2016,Ronald Baron,Bill F. Baron,
AB00046,06-30-2016,Ronald Baron,Bill F. Baron,
AB00046,07-31-2016,Ronald Baron,Bill F. Baron,
AB00046,08-31-2016,Ronald Baron,Bill F. Baron,
AB00046,09-30-2016,Ronald Baron,Bill F. Baron,
AB00046,10-31-2016,Ronald Baron,Bill F. Baron,Tim S.
AB00046,11-30-2016,Ronald Baron,Bill F. Baron,Tim S.
AB00046,12-31-2016,Ronald Baron,Bill F. Baron,Tim S.
AB00046,01-31-2017,Ronald Baron,Bill F. Baron,Tim S.
AB00046,02-28-2017,Ronald Baron,Bill F. Baron,Tim S.
AB00046,03-31-2017,Ronald Baron,Bill F. Baron,Tim S.
AB00046,04-30-2017,Ronald Baron,Bill F. Baron,Tim S.
AB00046,05-31-2017,Ronald Baron,Bill F. Baron,Tim S.
AB00046,06-30-2017,Ronald Baron,Bill F. Baron,Tim S.
AB00046,07-31-2017,Ronald Baron,Bill F. Baron,
AB00046,08-31-2017,Ronald Baron,Bill F. Baron,
AB00046,09-30-2017,Ronald Baron,Bill F. Baron,
AB00046,10-31-2017,Ronald Baron,Bill F. Baron,
AB00046,11-30-2017,Ronald Baron,Bill F. Baron,
AB00046,12-31-2017,Ronald Baron,Bill F. Baron,
AB00050,01-31-2016,
AB00050,02-28-2016,
AB00050,03-31-2016,
AB00050,04-30-2016,
AB00050,05-31-2016,
AB00050,06-30-2016,Sharon
AB00050,07-31-2016,Sharon
AB00050,08-31-2016,Sharon
AB00050,09-30-2016,Sharon
AB00050,10-31-2016,Sharon
AB00050,11-30-2016,Sharon
AB00050,12-31-2016,Sharon
AB00050,01-31-2017,Sharon
AB00050,02-28-2017,Sharon
AB00050,03-31-2017,Sharon
AB00050,04-30-2017,Sharon
AB00050,05-31-2017,Sharon
AB00050,06-30-2017,Sharon
AB00050,07-31-2017,Sharon
AB00050,08-31-2017,Sharon
AB00050,09-30-2017,Sharon
AB00050,10-31-2017,Sharon
AB00050,11-30-2017,Sharon
AB00050,12-31-2017,Sharon
;
run;

Please suggest some solution.

Thanks.

Ksharp · Posted 11-02-2018 10:03 AM

OK. You want [2016-10-26 -- 2017-06-01] become [2016-10-26 -- 2017-06-30]?

data want;
set have;
array x{*} $ Manager1-Manager3;
array y{*} $ 32 new_Manager1-new_Manager3;

do i=1 to dim(x);
if not missing(x{i}) then do;

start=input(scan(scan(x{i},1,'[]'),1,' '),?? yymmdd10.);
if start<Date_Month then start=Date_Month;

end=intnx('month',input(scan(scan(x{i},1,'[]'),-1,' '),?? yymmdd10.),0,'e');
if end>'31dec2017'd or missing(end) then end='31dec2017'd;

if start<=Date_Month<=end then y{i}=scan(x{i},-1,'[]');
end;
end;

drop start end i;
run;

View solution in original post

Saba1 · Posted 10-30-2018 01:39 AM

@Ksharp: I shall be highly grateful if you can guide me in solving this issue.

s_lassen · Posted 10-30-2018 05:53 AM

Something like this?

data parsed;
  set have;
  array managers(*) 8  manager1-manager3;
  array start(*) 8 start1-start3;
  array end(*) 8 end1-end3;
  array names(*) $60 name1-name3;
  do _N_=1 to 3;
    length str $60;
    str=scan(managers(_N_),1,'[]');
    start(_N_)=input(scan(str,1,' '),?? yymmdd10.);
    end(_N_)=input(scan(str,3,' '),?? yymmdd10.);
    names(_N_)=left(scan(managers(_N_),2,'[]'));
    if missing(start(_N_)) or start(_N_)>date_month then
      call missing(names(_N_));
    else do;
      if start(_N_)<date_month then
	    start(_N_)=date_month;
      if missing(end(_N_)) then
        end(_N_)=date_month;
      else if end(_N_)<date_month then
        call missing(end(_N_),start(_N_),names(_N_));
      end;
	end; 
  format start: end: yymmdd10.;
  keep id date_month name:;
  _I_=2;
  do _N_=1 to 3-cmiss(of name:); /* left justify NAMES array */
    if missing(names(_N_)) then do;
	  do _I_=_N_ to 3 until(not missing(names(_I_)));
	    end;
	  names(_N_)=names(_I_);
      call missing(names(_I_));
      end;
    end;
run;

I put the manager variables without dates into a new array, names. That was mostly to be able to debug, you can put the names back into the manager variables, if you want.

Saba1 · Posted 10-31-2018 02:40 AM

@s_lassen: Thank you so much for this favor. The code is really useful. However, I encounter two issues: First, the names of managers are not full in new variables. Second, for all the cases like "[2016-10-26 -- 2017-06-01] Tim S." where End date of the bracket is 2017-06-01, in the result dataset manager's name is reported till 2017-05-31, which means laps of one month. Can you figure out the reason and guide me accordingly? Thanks.

Ksharp · Posted 10-30-2018 10:02 AM

Ha. Maybe I understood your question.

Data Have;
infile datalines
dlm=","
missover
DSD;
input ID : $10.
	Date_Month : mmddyy10.
	Manager1 : $60.
	Manager2 : $60.
	Manager3 : $60. ;
format Date_Month mmddyy10. ;
datalines;
AB00046,01-31-2016,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,02-28-2016,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,03-31-2016,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,04-30-2016,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,05-31-2016,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,06-30-2016,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,07-31-2016,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,08-31-2016,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,09-30-2016,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,10-31-2016,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,11-30-2016,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,12-31-2016,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,01-31-2017,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,02-28-2017,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,03-31-2017,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,04-30-2017,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,05-31-2017,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,06-30-2017,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,07-31-2017,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,08-31-2017,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,09-30-2017,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,10-31-2017,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,11-30-2017,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00046,12-31-2017,[2012-01-23 -- ] Ronald Baron,[2016-01-23 -- ] Bill F. Baron,[2016-10-26 -- 2017-06-01] Tim S.
AB00050,01-31-2016,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,02-28-2016,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,03-31-2016,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,04-30-2016,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,05-31-2016,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,06-30-2016,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,07-31-2016,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,08-31-2016,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,09-30-2016,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,10-31-2016,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,11-30-2016,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,12-31-2016,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,01-31-2017,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,02-28-2017,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,03-31-2017,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,04-30-2017,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,05-31-2017,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,06-30-2017,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,07-31-2017,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,08-31-2017,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,09-30-2017,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,10-31-2017,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,11-30-2017,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
AB00050,12-31-2017,[2010-05-20 -- 2014-01-02] Tom T.,[2016-06-29 -- 2018-06-06] Sharon,
;
run;
data want;
 set have;
 array x{*} $ Manager1-Manager3;
 array y{*} $ 32 new_Manager1-new_Manager3;

 do i=1 to dim(x);
 if not missing(x{i}) then do;

 start=input(scan(scan(x{i},1,'[]'),1,' '),?? yymmdd10.);
 if start<Date_Month then start=Date_Month;

 end=input(scan(scan(x{i},1,'[]'),-1,' '),?? yymmdd10.);
 if end>'31dec2017'd or missing(end) then end='31dec2017'd;

 if start<=Date_Month<=end then y{i}=scan(x{i},-1,'[]');
 end;
 end;

 drop start end i;
run;

Saba1 · Posted 10-31-2018 02:34 AM

@Ksharp: Thanks a lot. The code is really useful and giving the required dataset. The only issue is with the cases like "[2016-10-26 -- 2017-06-01] Tim S.", where End date of the bracket is 2017-06-01, but in the resulting dataset manager's name is reported till 2017-05-31.This means laps of one month. Can you figure out the reason and guide me accordingly? Thanks.

Ksharp · Posted 10-31-2018 08:49 AM

But in your output , There is Tim.S ?

AB00046,05-31-2017,Ronald Baron,Bill F. Baron,Tim S.

Saba1 · Posted 11-01-2018 08:54 PM

@Ksharp: You are right. The name is correct. Actually, the issue highlighted in my last question is regarding the repetition of this manager's name according to bracket dates. As per the bracket dates, I want the output dataset where Tim S. is stating from 10/31/2016 and this name must finish on 06/30/2017 of Date_Month column. However, when I use your developed code, this name ends on 05/31/2017 and does not appear on 06/30/2017. So the issue of missing one month (ending date) is there. I hope now my question is understandable. Kindly guide me in this regard. Thanks.

Ksharp · Posted 11-02-2018 10:03 AM

OK. You want [2016-10-26 -- 2017-06-01] become [2016-10-26 -- 2017-06-30]?

data want;
set have;
array x{*} $ Manager1-Manager3;
array y{*} $ 32 new_Manager1-new_Manager3;

do i=1 to dim(x);
if not missing(x{i}) then do;

start=input(scan(scan(x{i},1,'[]'),1,' '),?? yymmdd10.);
if start<Date_Month then start=Date_Month;

end=intnx('month',input(scan(scan(x{i},1,'[]'),-1,' '),?? yymmdd10.),0,'e');
if end>'31dec2017'd or missing(end) then end='31dec2017'd;

if start<=Date_Month<=end then y{i}=scan(x{i},-1,'[]');
end;
end;

drop start end i;
run;

Saba1 · Posted 11-02-2018 08:24 PM

@Ksharp: Thank you so much. It works perfectly well now.

Saba1 · Posted 01-10-2019 04:10 AM

@Ksharp: Your mentioned code really have worked well. But now the issue is that resulting managers' names in columns (without date brackets) are not read as a character variable. Please guide me how can I format them to be a character variable , so that in future i can perform other workings on them. Thanks

Ksharp · Posted 01-15-2019 02:33 AM

Sorry. I can't understand you .

Can you post your data ? and better start a brand new session ,this could let others see it too.

If your 'Manager' is numeric type variable ,try change this

data want;
set have;
array x{*} $ Manager1-Manager3;
array y{*} $ 32 new_Manager1-new_Manager3;

====》

data want;
set have;
array x{*} Manager1-Manager3;
array y{*} new_Manager1-new_Manager3;

Saba1 · Posted 01-15-2019 09:14 PM

@Ksharp: Thanks a lot for replying. The issue has been resolved.

Tom · Posted 10-31-2018 09:29 AM

Why not first read your source data into a more normalized form.

data have;
infile datalines dlm='[,]' truncover ;
length id $10 date_month start_dt stop_dt manager_num 8 manager $60 date_period $25 ;
informat date_month mmddyy.;
format date_month start_dt stop_dt yymmdd10. ;
input id date_month @;
do manager_num=1 by 1 until (manager=' ');
  input date_period manager @ ;
  if manager ne ' ' then do;
    start_dt = input(date_period,yymmdd10.);
    stop_dt  = input(substr(date_period,15),yymmdd10.);
    output;
  end;
end;
datalines;

                                                       manager_
Obs     id      date_month     start_dt      stop_dt      num     manager         date_period

  1   AB00046   2016-01-31   2012-01-23            .       1      Ronald Baron    2012-01-23 --
  2   AB00046   2016-01-31   2016-01-23            .       2      Bill F. Baron   2016-01-23 --
  3   AB00046   2016-01-31   2016-10-26   2017-06-01       3      Tim S.          2016-10-26 -- 2017-06-01
  4   AB00046   2016-02-28   2012-01-23            .       1      Ronald Baron    2012-01-23 --
  5   AB00046   2016-02-28   2016-01-23            .       2      Bill F. Baron   2016-01-23 --
  6   AB00046   2016-02-28   2016-10-26   2017-06-01       3      Tim S.          2016-10-26 -- 2017-06-01
  7   AB00046   2016-03-31   2012-01-23            .       1      Ronald Baron    2012-01-23 --
  8   AB00046   2016-03-31   2016-01-23            .       2      Bill F. Baron   2016-01-23 --
  9   AB00046   2016-03-31   2016-10-26   2017-06-01       3      Tim S.          2016-10-26 -- 2017-06-01

You can then apply your filtering rules to that and then, if you really need to, rotate it back into the "wide" form.

Dealing with Character Variable with matched dates

Re: Dealing with Character Variable with matched dates

Re: Dealing with Character Variable with matched dates

Re: Dealing with Character Variable with matched dates

Re: Dealing with Character Variable with matched dates

Re: Dealing with Character Variable with matched dates

Re: Dealing with Character Variable with matched dates

Re: Dealing with Character Variable with matched dates

Re: Dealing with Character Variable with matched dates

Re: Dealing with Character Variable with matched dates

Re: Dealing with Character Variable with matched dates

Re: Dealing with Character Variable with matched dates

Re: Dealing with Character Variable with matched dates

Re: Dealing with Character Variable with matched dates

Re: Dealing with Character Variable with matched dates

Classroom Training Available!