BookmarkSubscribeRSS Feed
tony_vanhorn_usoc_org
Calcite | Level 5

I have data like this:

GroupStart DateEnd Date
A01JAN201402JAN2014
B02JAN201404JAN2014

 

From the table above I'd like to create an output table for each group that would look like the following:

output table A

GroupDate
A01JAN2014
A02JAN2014

output table B

GroupDate

B

02JAN2014
B03JAN2014
B04JAN2014

As you can see, it's simply stacking data and then creating/populating rows between a start date and end date.  I'm looking to do this type of manipulation in either enterprise guide or jmp so suggestions for either approach are welcomed.

4 REPLIES 4
Reeza
Super User

I'd probably recommend EG and code, rather than a point and click. I'm sure there's a way with point and click but I can't think of it at the moment.

Why do you need separate data sets by the way? It's probably better to keep that date in and use BY groups in SAS EG instead.

Here's the flip.

data want;

set have;

do date=start_date to end_date;

output;

end;

drop start_date end_date;

run;

Haikuo
Onyx | Level 15

With two steps, there are probably many solutions available. Hash is able to offer a dynamic one-step solution. The only maybe caveat is the data format can't be kept.

data have;

input Group$      (Start_Date End_Date) (:date9.);

format Start_Date End_Date date9.;

cards;

A     01JAN2014 02JAN2014

B     02JAN2014 04JAN2014

;

data _null_;

  declare hash h(ordered:'a',multidata:'y');

  h.definekey('date');

  h.definedata('group','date');

  h.definedone();

  set have;

   do date=start_date to end_date;

    rc=h.add();

  end;

   rc=h.output(dataset:group);

run;


Haikuo


UPdate: OOPS. Failed to understand OP's question first time around. Updated per Reeza's answer. Hopefully works this time.

tony_vanhorn_usoc_org
Calcite | Level 5

So I'm liking the results however, how could I retain the specified date format (date9.) when splitting the output into separate files?

Tom
Super User Tom
Super User

One way to make sure the variable has the same format is to create it by renaming an existing variable.

This code will do what you want (make sure that values of GROUP are valid dataset names).

If you only have one record per group then you do not need to do until loop.

data _null_;

  if 0 then set have (keep=group start rename=(start=date));

  if _n_=1 then do;

      declare hash h(ordered:'a',multidata:'y');

      h.definekey('date');

      h.definedata('group','date');

      h.definedone();

  end;

  do until(last.group) ;

    set have ;

    by group ;

    do date= start to end ;

      rc=h.add();

    end ;

  end;

  rc=h.output(dataset:group);

  rc=h.clear();

run;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 873 views
  • 6 likes
  • 4 in conversation