BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Emma8
Quartz | Level 8
Hi.
1 ACCEPTED SOLUTION

Accepted Solutions
LeonidBatkhan
Lapis Lazuli | Level 10

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.

 

View solution in original post

8 REPLIES 8
ballardw
Super User

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
Quartz | Level 8

Please find the Simplified data in below text box

ballardw
Super User

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.

Emma8
Quartz | Level 8
 
ballardw
Super User

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
Lapis Lazuli | Level 10

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
Quartz | Level 8
Wow that was really helpful and works exactly what I wanted. Thank you so much!

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 8 replies
  • 1329 views
  • 2 likes
  • 4 in conversation