topic Re: how to pivot data in enterprise miner? in SAS Data Science

how to pivot data in enterprise miner?

userMB — Fri, 15 May 2015 15:31:40 GMT

hello

im new to enterprise miner;

id like to know if this is posible to achieve the following in enterprise miner:

source data format:

policy_id policy_type customer_id

1 house 1

2 vehicle 1

3 life 1

4 house 2

5 vehicle 2

6 life 2

desired layout

customer_id no_of_policies_house no_of_policies_vehicle no_of_policices_life

1 1 1 1

2 1 1 1

if yes, how can this be achieved?

i apprieciate any tips

thx

rgds

Re: how to pivot data in enterprise miner?

jakarman — Fri, 15 May 2015 18:42:05 GMT

The Eminer is doing predictive analytics on prepared data. Preparing data is better to do with Eguide running SAS-base code. Yes you could define basic code nodes in Eminer as you could bake eggs on an Ipad.

Re: how to pivot data in enterprise miner?

M_Maldonado — Mon, 18 May 2015 14:55:47 GMT

Hey MB,

I don't think there is a way to transpose your data automatically in Enterprise Miner. Same as Jaap, I would prefer to do it in base SAS or Enterprise Guide.

How complex is this SAS transpose program that we need to write? Do you have a finite number of policy_type (house, vehicle, life), or your SAS program has to catch that? Are your records in order (by customer id)?

To get the transposed data set you want I would use a SAS data step with arrays.

This paper will get you started: Sharpening your skills in reshaping data: proc transpose vs array processing

If you run into trouble, the guys on this community are the bomb! SAS support community: SAS Macro, Data Step, and SAS Language Elements

This book is a fantastic read to start doing complex, efficient SAS programs in no time. Carpenter's Complete Guide to the SAS Macro Language

Good luck!

-Miguel