How to "compress" a dataset

[obertel]

Hi Guru's

In Enterprise Guide my dataset looks like this

              Date1    Date2    Date3

A                     01/01/17 

A                             01/02/17

A            01/03/17

A            01/04/17

B            01/01/17

B                             02/01/17

I want to "compress" the dataset to this:

              Date1    Date2    Date3

A             01/03/17 01/01/17 01/02/17

A             01/04/17

B             01/01/17          02/01/17

or alternative:

              Date1    Date2    Date3

A             01/03/17 01/01/17 01/02/17

A             01/04/17 01/01/17 01/02/17

B             01/01/17          02/01/17

Any good ideas?

/Ole

[LinusH]

What does the dates mean?
Why are they stored like that?
And what would your new structure tell from a business perspective?

[obertel]

Hi

Here's some business context (very simplified)

Date1 = Date for received phone call

Date2 = Date for outgoing phone call

Date3 = Date for received email

A = Customer A

B = Customer B

Dates are SAS dates.

/Ole

[Patrick]

@obertel

From a data organisation point of view it would be much better to re-structure your data like {customer, date, type}. 

Is that an option for you and if yes do you need help with the code for that?

[Kurt_Bremser]

I'd much rather have the fields customer, date and comm_type, where comm_type has values for incoming/outgoing call, received/sent email, and so on. A new communication type won't need an expansion of the structure, everything is done via data.

As a side-effect, you will always (by sorting by date) have a clear picture of the history of a customer; the wide format will quite effectively prevent that.

[obertel]

Hi

Thanks for your suggestions.

I have no problem structuring the data in customer, type and date.

The challenge is the presentation layer, where I want to present the dates of three specific types in columns.

/Ole

[Kurt_Bremser · Accepted solution]

Using your original structure for an example, the following should do it:

data have;
infile cards dlm=',' dsd truncover;
input customer $ (date1-date3) (:mmddyy8.);
format date1-date3 mmddyy10.;
cards;
A,,01/01/17 
A,,,01/02/17
A,01/03/17
A,01/04/17
B,01/01/17
B,,,02/01/17
;
run;

data
  date1 (keep=customer date1)
  date2 (keep=customer date2)
  date3 (keep=customer date3)
;
set have;
if date1 ne . then output date1;
if date2 ne . then output date2;
if date3 ne . then output date3;
run;

data want;
if _n_ = 0 then set have; * this does nothing except defining the variables, so that "of _all_" in the next line works;
call missing(of _all_); 
merge
  date1
  date2
  date3
;
by customer;
run;

[obertel]

Hi Kurt

Your solution works perfect. Though, I had to sort date1, date2 and date3 dataset before the merge.

Thank you for your help and thanks to all for your input.

/Ole

[LinusH]

What is your presentation layer?

Most tools/many SAS PROCs have ability to to cross tabulation using categorical values (but you don't seem to have any measures, this is more of a list?).

Also, even if you are transposing up the values on date category, you'll stil don't have a clear key in the table, and your result table with have an unclear structure/granularity.

[Ksharp]

data have;
infile cards dlm=',' dsd truncover;
input customer $ (date1-date3) (:mmddyy8.);
format date1-date3 mmddyy10.;
cards;
A,,01/01/17 
A,,,01/02/17
A,01/03/17
A,01/04/17
B,01/01/17
B,,,02/01/17
;
run;
data want;
 update have(obs=0) have;
 by customer;
 if last.customer or nmiss(of date:)=0 then output;
run;