SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

data transformation task involving start and end dates

Reply
New Contributor
Posts: 4

data transformation task involving start and end dates

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.

Super User
Posts: 19,877

Re: data transformation task involving start and end dates

Posted in reply to tony_vanhorn_usoc_org

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;

Respected Advisor
Posts: 3,156

Re: data transformation task involving start and end dates

Posted in reply to tony_vanhorn_usoc_org

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.

New Contributor
Posts: 4

Re: data transformation task involving start and end dates

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

Super User
Super User
Posts: 7,079

Re: data transformation task involving start and end dates

Posted in reply to tony_vanhorn_usoc_org

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;

Ask a Question
Discussion stats
  • 4 replies
  • 331 views
  • 6 likes
  • 4 in conversation