topic Re: How to "compress" a dataset in SAS Enterprise Guide

How to "compress" a dataset

obertel — Thu, 14 Dec 2017 07:11:04 GMT

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

Re: How to "compress" a dataset

LinusH — Thu, 14 Dec 2017 07:13:25 GMT

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

Re: How to "compress" a dataset

obertel — Thu, 14 Dec 2017 07:21:40 GMT

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

Re: How to "compress" a dataset

Patrick — Thu, 14 Dec 2017 08:11:36 GMT

@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?

Re: How to "compress" a dataset

Kurt_Bremser — Thu, 14 Dec 2017 08:37:30 GMT

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.

Re: How to "compress" a dataset

obertel — Thu, 14 Dec 2017 08:52:21 GMT

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

Re: How to "compress" a dataset

Kurt_Bremser — Thu, 14 Dec 2017 09:44:49 GMT

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;

Re: How to "compress" a dataset

LinusH — Thu, 14 Dec 2017 10:12:36 GMT

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.

Re: How to "compress" a dataset

obertel — Thu, 14 Dec 2017 11:04:55 GMT

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

Re: How to "compress" a dataset

Ksharp — Thu, 14 Dec 2017 12:48:49 GMT

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;