stuck at the first hurdle

[PauliF]

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

[goladin]

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