stuck at the first hurdle

PauliF — Fri, 05 Nov 2010 09:05:28 GMT

Hi everyone

I am new to SAS and EG although I have a lot of statistics experience I have just seemed to accidentally avoid this software so far

anyway I have done the tutorial and have SAS for dummies and the little book of SAS ...

but I am stumped at my first hurdle

the data I have is in rows of transactions not per customer...

so the first column is customer reference number and each customer typically has 3-6 rows showing each transaction.

(some have just 1 and some have as many as 20)

I want to create just one row per customer

there are columns after customer number recording some information

lets call them a, b, c, d, e and f

they have numbers but some of the numbers are not amounts they are area codes or other forms of classification (so I don't want to sum them)

what I want in my new data set is a number of columns:

i.e

policy number, a, b, c, d, e, f, a1, b1, c1, d1, e1, f1, a2, b2, c2, d2, e2, f2,........ a20, b20, c20, d20, e20, f20

I know this is an ETL task but I cannot get my data in any other format.

Is there a relatively easy way to do this?

I would know how to do it in Excel but I have a couple of million lines of data.

any help would be hugely appreciated

many thanks in advance

each customer ends up having

Re: stuck at the first hurdle

goladin — Fri, 05 Nov 2010 12:10:30 GMT

Hi,

Based on your comments, I would recommend you to use a transpose step for each variable. By doing a simple transpose for each ID and variable, you can receive a row for each customer and an array for each column of variable. After creating these files, you can then merge them together to form a single file.

However, the best possible approach is actually to use a simple data step with retain statement and by statements to generate this data.

Regards,
Murphy

topic stuck at the first hurdle in SAS Enterprise Guide

stuck at the first hurdle

Re: stuck at the first hurdle