topic Re: data transformation task involving start and end dates in SAS Data Management

data transformation task involving start and end dates

tony_vanhorn_usoc_org — Thu, 13 Mar 2014 22:58:56 GMT

I have data like this:

Group	Start Date	End Date
A	01JAN2014	02JAN2014
B	02JAN2014	04JAN2014

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

output table A

Group	Date
A	01JAN2014
A	02JAN2014

output table B

Group	Date
B	02JAN2014
B	03JAN2014
B	04JAN2014

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.

Re: data transformation task involving start and end dates

Reeza — Thu, 13 Mar 2014 23:55:09 GMT

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;

Re: data transformation task involving start and end dates

Haikuo — Fri, 14 Mar 2014 00:11:42 GMT

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.

Re: data transformation task involving start and end dates

tony_vanhorn_usoc_org — Fri, 14 Mar 2014 20:31:28 GMT

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

Re: data transformation task involving start and end dates

Tom — Fri, 14 Mar 2014 22:31:33 GMT

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;