data

[Emma8]

Hi.

[Kurt_Bremser]

Please post SAS datasets in SAS code (data step with datalines). Keep in mind that this is not an Excel support forum.

[ballardw]

One basic program:

Proc sql;
   create table names as
   select distinct name
   from have
   ;
quit;

data want;
   set names;
   do date = "28FEB2020"D to"03MAR2020"D;;
      output;
   end;
   format date date9.;
run;

proc sort data=want;
   by date name;
run;

Though I probably wouldn't use that sort order personally.

If you really must use macro variables for the start and end it should be obvious where they go.

I would strongly recommend using a two digit day of the month so there is no doubt whether 3Mar was actually intended to be 30Mar.

[Emma8]

Please find the Simplified data in below text box

[ballardw]

I  refuse to generate anything with 2 digit years and really dislike one digit days of months. Formats are transient. You can use any format you want. ddmmyy6. instead of date9.

If the order of the names is hyper critical then you better specify that as a requirement.

[ballardw]

Create a second set with the start end periods as I did and append to your existing data.

I think.

Not seeing a clearly stated difference.

[LeonidBatkhan · Accepted solution]

Hi Emma8,

First of all, your first data step HAVE is not correct, it does not read your datalines. Second of all, if you are producing DATEs defined by macro variables start and end, those DATEs defined in the first data step are irrelevant, you need to generate your new DATEs.

Here is the code I believe will do what you want:

%let start="28FEB2020"D;
%let end="3MAR2020"D;

data HAVE;
   length NAME $10;
   infile datalines delimiter=':';
   input NAME;
   datalines;
Anna, Josh: 28/02/2020
John: 28/02/2020
Anna, Josh: 29/02/2020
John: 29/02/2020
Kate: 29/02/2020
Kate: 29/02/2020
John: 01/03/2020
Kate: 01/03/2020
;

proc sort data=HAVE out=UNIQUE_NAMES(keep=NAME) nodupkey;
   by NAME;
run;

data WANT;
   set UNIQUE_NAMES;
   do DATE=&start to &end;
      output;
   end;
   format DATE ddmmyy10.;
run;

proc sort data=WANT;
   by DATE;
run;

Hope this helps.

[Emma8]

Wow that was really helpful and works exactly what I wanted. Thank you so much!