Combining multiple rows of data into a single row for each unique ID

  • Permalink
  • submit to reddit
  • Email
  • Follow


I am a rank beginner using SPSS and need some step-by-step help.  I
have a very large (over one million records) file which contains
detailed data some of which is in multiple rows for the same ID.  I
have a unique ID variable and I have a Line Number variable.  What I
need to be able to do is to bring all of the lines into a single line
for each ID and then match it to a header file which also contains the
unique ID number.  I have been trying for two days using the
casestovars and varstocases commands as well as trying to split the
file by layering the id numbers, etc. and can not get anything to
work.  I have taken only one class in SPSS and we did not really
address how to use syntax - everything is point and click.  I am a
previous SAS user and find this extremely frustrating and would
appreciate any help anyone can give me.

0
Reply Jan 7/20/2007 5:26:26 PM

See related articles to this posting


Jan wrote:
> I am a rank beginner using SPSS and need some step-by-step help.  I
> have a very large (over one million records) file which contains
> detailed data some of which is in multiple rows for the same ID.  I
> have a unique ID variable and I have a Line Number variable.  What I
> need to be able to do is to bring all of the lines into a single line
> for each ID and then match it to a header file which also contains the
> unique ID number.  I have been trying for two days using the
> casestovars and varstocases commands as well as trying to split the
> file by layering the id numbers, etc. and can not get anything to
> work.  I have taken only one class in SPSS and we did not really
> address how to use syntax - everything is point and click.  I am a
> previous SAS user and find this extremely frustrating and would
> appreciate any help anyone can give me.
> 


It would be helpful if you posted a small sample of the current data 
(e.g., 3 or 4 IDs with 4 or 5 variables) along with how you want it to 
look after restructuring.

-- 
Bruce Weaver
bweaver@lakeheadu.ca
www.angelfire.com/wv/bwhomedir
"When all else fails, RTFM."
0
Reply Bruce 7/20/2007 6:45:33 PM

On Jul 20, 1:45 pm, Bruce Weaver <bwea...@lakeheadu.ca> wrote:
> Jan wrote:
> > I am a rank beginner using SPSS and need some step-by-step help.  I
> > have a very large (over one million records) file which contains
> > detailed data some of which is in multiple rows for the same ID.  I
> > have a unique ID variable and I have a Line Number variable.  What I
> > need to be able to do is to bring all of the lines into a single line
> > for each ID and then match it to a header file which also contains the
> > unique ID number.  I have been trying for two days using the
> > casestovars and varstocases commands as well as trying to split the
> > file by layering the id numbers, etc. and can not get anything to
> > work.  I have taken only one class in SPSS and we did not really
> > address how to use syntax - everything is point and click.  I am a
> > previous SAS user and find this extremely frustrating and would
> > appreciate any help anyone can give me.
>
> It would be helpful if you posted a small sample of the current data
> (e.g., 3 or 4 IDs with 4 or 5 variables) along with how you want it to
> look after restructuring.
>
> --
> Bruce Weaver
> bwea...@lakeheadu.cawww.angelfire.com/wv/bwhomedir
> "When all else fails, RTFM."



Thanks, Bruce.  Here is an example of what the data looks like except
there are multiple variables that I have not included to the right of
HPC_3.

Prov__ID            Line_No         HPC_1          HPC_2
HPC_3
5142                             1          6391
1785            1452
9865                             1          7784
5241             6999
9865                             2          6302
8574             1111

This is what I'd like it to end up looking like:

Prov_ID              N_Count        HPC_1          HPC_2
HPC_3        HPC_xxx        HPC_xxx        HPC_xxx
9865                             2            7784
5241            6999           6302           8574             1111

This would tell me how many lines make up the group and move the 3
values from the 2nd line to the same row as the values from the first
row.  Another factor is that some of the cases (by Prov_ID) have
various numbers of lines - some
have 2 lines and some have 9 lines so I'm not sure how to tell SPSS to
look for all that may be there and name a new
variable accordingly. I don't quite know where to start and I'm the
only SPSS person here.  Thanks so much for any guidance you can give
me.




0
Reply Jan 7/20/2007 7:08:52 PM

There are 2 steps.

1. You can use AGGREGATE and N_Break command (Data...Aggregate). Use
Prov_ID as a Break Variable and click N_Break.  It will generate a new
variable counting the number of rows within Prov_ID.

***************************************************************************
This is what you got before.

> Prov__ID   Line_No   HPC_1   HPC_2   HPC_3
> 5142            1          6391      1785      1452
> 9865            1          7784      5241      6999
> 9865            2          6302      8574      1111

This is what you will get after.

> Prov__ID   Line_No   HPC_1   HPC_2   HPC_3   N_Break
> 5142            1          6391      1785      1452        1
> 9865            1          7784      5241      6999        2
> 9865            2          6302      8574      1111        2
**************************************************************************

2. Use Restructure command (from Data...Restructure).  Select
"Restructure Cases into Variables", use Prov_ID as an identifier.

The new data file will look like this, which is not exactly the same
as the one you want because of the order of the variable.  However,
you can tell SPSS arrange the variables in any order that you want.

> Prov_ID  N_Count   HPC_1.1   HPC_1.2   HPC2.1   HPC2.2   HPC_3.1   HPC3.2
> 9865      2              7784         6302         5241       8574        6999        1111
> 5142      1              6391                         1785                      1452


> Prov_ID  N_Count   HPC_1   HPC_2    HPC_3   HPC_xxx   HPC_xxx   HPC_xxx
> 9865      2              7784      5241       6999      6302          8574         1111
> 5142      1              6391      1785      1452

0
Reply TASTAT 7/20/2007 8:11:23 PM

Jan wrote:
> On Jul 20, 1:45 pm, Bruce Weaver <bwea...@lakeheadu.ca> wrote:
>> Jan wrote:
>>> I am a rank beginner using SPSS and need some step-by-step help.  I
>>> have a very large (over one million records) file which contains
>>> detailed data some of which is in multiple rows for the same ID.  I
>>> have a unique ID variable and I have a Line Number variable.  What I
>>> need to be able to do is to bring all of the lines into a single line
>>> for each ID and then match it to a header file which also contains the
>>> unique ID number.  I have been trying for two days using the
>>> casestovars and varstocases commands as well as trying to split the
>>> file by layering the id numbers, etc. and can not get anything to
>>> work.  I have taken only one class in SPSS and we did not really
>>> address how to use syntax - everything is point and click.  I am a
>>> previous SAS user and find this extremely frustrating and would
>>> appreciate any help anyone can give me.
>> It would be helpful if you posted a small sample of the current data
>> (e.g., 3 or 4 IDs with 4 or 5 variables) along with how you want it to
>> look after restructuring.
>>
>> --
>> Bruce Weaver
>> bwea...@lakeheadu.cawww.angelfire.com/wv/bwhomedir
>> "When all else fails, RTFM."
> 
> 
> 
> Thanks, Bruce.  Here is an example of what the data looks like except
> there are multiple variables that I have not included to the right of
> HPC_3.
> 
> Prov__ID            Line_No         HPC_1          HPC_2
> HPC_3
> 5142                             1          6391
> 1785            1452
> 9865                             1          7784
> 5241             6999
> 9865                             2          6302
> 8574             1111
> 
> This is what I'd like it to end up looking like:
> 
> Prov_ID              N_Count        HPC_1          HPC_2
> HPC_3        HPC_xxx        HPC_xxx        HPC_xxx
> 9865                             2            7784
> 5241            6999           6302           8574             1111
> 
> This would tell me how many lines make up the group and move the 3
> values from the 2nd line to the same row as the values from the first
> row.  Another factor is that some of the cases (by Prov_ID) have
> various numbers of lines - some
> have 2 lines and some have 9 lines so I'm not sure how to tell SPSS to
> look for all that may be there and name a new
> variable accordingly. I don't quite know where to start and I'm the
> only SPSS person here.  Thanks so much for any guidance you can give
> me.
> 

I think the syntax shown below gives the result you ask for.  Copy it 
into a new syntax window (Open-->Syntax--New), then highlight it and run.

* ------------------------- .
data list list / Prov__ID  Line_No  HPC_1   HPC_2  HPC_3 (5f5.0).
begin data
5142      1        6391    1785   1452
9865      1        7784    5241   6999
9865      2        6302    8574   1111
end data.
list.

* Restructure the data from Cases to Vars .

SORT CASES BY Prov__ID Line_No .
CASESTOVARS
  /ID = Prov__ID
  /INDEX = Line_No
  /GROUPBY = INDEX
  /COUNT = N_count .

list.
* ------------------------- .

Here's the output:

Prov__ID N_count HPC_1.1 HPC_2.1 HPC_3.1 HPC_1.2 HPC_2.2 HPC_3.2

    5142       1    6391    1785    1452       .       .       .
    9865       2    7784    5241    6999    6302    8574    1111

Number of cases read:  2    Number of cases listed:  2


HTH.

-- 
Bruce Weaver
bweaver@lakeheadu.ca
www.angelfire.com/wv/bwhomedir
"When all else fails, RTFM."
0
Reply Bruce 7/20/2007 8:26:49 PM

On Jul 20, 3:26 pm, Bruce Weaver <bwea...@lakeheadu.ca> wrote:
> Jan wrote:
> > On Jul 20, 1:45 pm, Bruce Weaver <bwea...@lakeheadu.ca> wrote:
> >> Jan wrote:
> >>> I am a rank beginner using SPSS and need some step-by-step help.  I
> >>> have a very large (over one million records) file which contains
> >>> detailed data some of which is in multiple rows for the same ID.  I
> >>> have a unique ID variable and I have a Line Number variable.  What I
> >>> need to be able to do is to bring all of the lines into a single line
> >>> for each ID and then match it to a header file which also contains the
> >>> unique ID number.  I have been trying for two days using the
> >>> casestovars and varstocases commands as well as trying to split the
> >>> file by layering the id numbers, etc. and can not get anything to
> >>> work.  I have taken only one class in SPSS and we did not really
> >>> address how to use syntax - everything is point and click.  I am a
> >>> previous SAS user and find this extremely frustrating and would
> >>> appreciate any help anyone can give me.
> >> It would be helpful if you posted a small sample of the current data
> >> (e.g., 3 or 4 IDs with 4 or 5 variables) along with how you want it to
> >> look after restructuring.
>
> >> --
> >> Bruce Weaver
> >> bwea...@lakeheadu.cawww.angelfire.com/wv/bwhomedir
> >> "When all else fails, RTFM."
>
> > Thanks, Bruce.  Here is an example of what the data looks like except
> > there are multiple variables that I have not included to the right of
> > HPC_3.
>
> > Prov__ID            Line_No         HPC_1          HPC_2
> > HPC_3
> > 5142                             1          6391
> > 1785            1452
> > 9865                             1          7784
> > 5241             6999
> > 9865                             2          6302
> > 8574             1111
>
> > This is what I'd like it to end up looking like:
>
> > Prov_ID              N_Count        HPC_1          HPC_2
> > HPC_3        HPC_xxx        HPC_xxx        HPC_xxx
> > 9865                             2            7784
> > 5241            6999           6302           8574             1111
>
> > This would tell me how many lines make up the group and move the 3
> > values from the 2nd line to the same row as the values from the first
> > row.  Another factor is that some of the cases (by Prov_ID) have
> > various numbers of lines - some
> > have 2 lines and some have 9 lines so I'm not sure how to tell SPSS to
> > look for all that may be there and name a new
> > variable accordingly. I don't quite know where to start and I'm the
> > only SPSS person here.  Thanks so much for any guidance you can give
> > me.
>
> I think the syntax shown below gives the result you ask for.  Copy it
> into a new syntax window (Open-->Syntax--New), then highlight it and run.
>
> * ------------------------- .
> data list list / Prov__ID  Line_No  HPC_1   HPC_2  HPC_3 (5f5.0).
> begin data
> 5142      1        6391    1785   1452
> 9865      1        7784    5241   6999
> 9865      2        6302    8574   1111
> end data.
> list.
>
> * Restructure the data from Cases to Vars .
>
> SORT CASES BY Prov__ID Line_No .
> CASESTOVARS
>   /ID = Prov__ID
>   /INDEX = Line_No
>   /GROUPBY = INDEX
>   /COUNT = N_count .
>
> list.
> * ------------------------- .
>
> Here's the output:
>
> Prov__ID N_count HPC_1.1 HPC_2.1 HPC_3.1 HPC_1.2 HPC_2.2 HPC_3.2
>
>     5142       1    6391    1785    1452       .       .       .
>     9865       2    7784    5241    6999    6302    8574    1111
>
> Number of cases read:  2    Number of cases listed:  2
>
> HTH.
>
> --
> Bruce Weaver
> bwea...@lakeheadu.cawww.angelfire.com/wv/bwhomedir
> "When all else fails, RTFM."- Hide quoted text -
>
> - Show quoted text -

THANK YOU SO MUCH!!!!!

0
Reply Jan 7/20/2007 8:35:43 PM
comp.soft-sys.stat.spss 5459 articles. 1 followers. Post

5 Replies
20 Views

Similar Articles

[PageSpeed] 19


  • Permalink
  • submit to reddit
  • Email
  • Follow


Reply:

Similar Artilces:

script to combine multiple rows into a single row
Hi everyone, I really appreciate if anyone could help me with this tricky problem that I'm having. I'm looking for a sample script to combine data in multiple rows into one row. I'm using sqlserver. This is how data is stored in the table. ID Color 111 Blue 111 Yellow 111 Pink 111 Green This is the result that I would like to have. ID Color 111 Blue, Yellow, Pink, Green There is no definite number of colors per ID. I have to use ID to group these colors into one row. Therefore, ID becomes a unique key in the table. Appreciate your help and time. ...

Move data from multiple rows into single row.
Hi Everyone, Hope I'll able to explain clearly what I want. Here is the task; I have data in the table as shown below Sample1 activity1 date1 duration1 Sample1 activity1 date1 duration2 Sample1 activity2 date1 duration3 Sample2 activity1 date2 durationx Sample2 activity1 date2 durationy I want to rearrange (restructure) the data as follows Sample1 activity1 date1 duration1 duration2 Sample1 activity2 date1 duration3 Sample2 activity1 date2 durationx duratio...

Script to combine multiple rows into 1 single row
Hi, I'm working on a system migration and I need to combine data from multiple rows (with the same ID) into one comma separated string. This is how the data is at the moment: Company_ID Material 0x00C00000000053B86 Lead 0x00C00000000053B86 Sulphur 0x00C00000000053B86 Concrete I need it in the following format: Company_ID Material 0x00C00000000053B86 Lead, Sulphur, Concrete There is no definite number of materials per Company. I have read the part of http://www.sommarskog.se/arrays-in-s...

Combining multiple rows in query result to single row for reports
Hi All: I have an access db tracking portfolio deals with employees working on the deal. Usually there are multiple emplooyees involved, but no more than 4 or 5. i use an employee initial field to assign to the deals in a linked table. I am trying to create a query(and subsequent report) that will list the deals with the employee initials in a single row of the column separated by commas, thereby keeping a single entry for the deal in the report. However, I can't for the life of me create the continuous string function to provide these results. Any help would be appreciated. Regard...

Combine multiple records into single row
This is how the data is organized: vID Answer 12 Satisfied 12 Marketing 12 Yes 15 Dissatisfied 15 Technology 15 No 32 Strongly Dissatisfied 32 Marketing 32 Yes What I need to do is pull a recordset which each vID is a single row and each of the answers is a different field in the row so it looks something like this vID Answer1 Answer2 Answer3 12 Saitsfied Marketing Yes etc... I can't quite get my mind wrapped around this one. Examples: http://builder.com.com/5100-6388_14-6143761.html PIVOT will only work if you have...

multiple columns values in a single row of an uniq id...
Hi everybody, I have data like below.. --------------------------------------- empid itm no amount --------------------------------------- 01112655, 014, 6, 22365.00 01112654, 075, 1, 6205.00 01112654, 091, 24, 96200.00 01112654, 149, 2, 12217.00 01112654, 179, 14, 100645.00 01112654, 185, 5, 46172.00 01112654, 188, 8, 265000.00 01112654, 191, 4, 167000.00 01112654, 193, 1, 10000.00 01140654, 075, 1, 3151.00 01140654, 089, 2, 8236.00 01140654, 091, 9, 17662.00 01140654, 102, 3, 7568.00 01140654, 133, 1, 319.00 01140654, 1...

Help with combining data from multiple rows into one column in a view
Hi, I am stumped and was hoping someone could help me out. Any help is appreciated. I have a view that looks sort of like this (but with a lot more entries of course) UniqueIdentifyier Column1 Column2 1 9999 100 2 9999 200 3 9999 300 What I want to do is to add a column to the view that will contain a list of the values from column 2 where column 1 is the same. UniqueIdentifyier Column1 Column2 Column3 1 9999 100 100, 200, 300 2 9999 200 100, 200, 300 3 9999 300 100, 200, 300 (gaholmes@comcast....

Returning single column, multiple rows in a single row
Hi All, I'm a SQL newbie, so please be gentle with me! We have a CRM system with Potentials and Contacts, and my boss wants a report laid out with one row per potential including contacts, the issue being that each potential has zero or more contacts which are held in a separate table linked by a contact-potential-relationship table, ! So I need to see something like, potential name, amount, "contact list", notes, etc. etc. I searched the web and came across this solution in Oracle - http://www.dba-oracle.com/t_display_multiple_column_values_same_rows.htm, but I can't see h...

Return Single Row with Multiple Cols Instead of Multiple Row
Guys, Is it possible to return single row with multiple columns instead of multiple rows. Basically, query returns following hierarchy. product classification parent Classification ABC Motorcycle Motorcycle ABC On Road Motorcycle ABC Casual On Road ABC Model Casual Hierarchy is not fixed level,and values of classification change based on product. Thank you in advance, Anil G Anil G wrote: > Is it possible to return single row with multiple columns instead of > multiple rows. > ...

Combine matching multiple rows into one row
IS there a way to combine all matching rows in a table so that it outputs as one row, for example: tblMyStuff UniqueID int IDENTITY ParentID int SomeSuch nvarchar(50) SomeSuch2 nvarchar(50) Table data: UniqueID ParentID SomeSuch SomeSuch2 1 1 Dog Bark 2 1 Cat Meow 3 3 Cow Moo 4 3 Horse Whinnie 5 5 Pig Oink Desired query result from Query: SELECT ??? as myText from tblMyStuff WHERE ParentID = 3 myText = Cow Moo, Horse Whinnie Help is appreciated, lq laurenq uantrell (laurenquantrell@hotmail.com) w...

Combine column multiple rows into one row
Is it possible to combine multiple rows returned from select statement into one row? SELECT NAME FROM TABLE1; I want all names to be combined into one row seperated by commas. Antanas wrote: > Is it possible to combine multiple rows returned from select statement > into one row? > > SELECT NAME FROM TABLE1; > > I want all names to be combined into one row seperated by commas. Use recursive SQL to do the concatenation. Examples are posted here every few weeks... -- Knut Stolze DB2 Information Integration Development IBM Germany Thanks Knut. ...

Concatenate multiple rows into a single row
Can someone please help me out in writing the Sql to concatenate the Text_desc for each code for all the seq_nos in the ascending order of seq_no and load into the target table. Source table : S1 Code Seq_no Text_desc --------------------------- 1579 0 BCN5, CO20, ER75 1579 1 FP5, WC6, MATW, OPRH 1579 2 T 1600 0 TW, FP5, AS5, MHSA15, DME20 1600 1 ER100, UR40 Target table: T1 Text_desc (concatenate all the strings for each code in ascending order of seq_no) code Text_desc ----- -------------...

function to convert multiple rows to single row available?
Is there any function in unix that can conveniently convert the followings A asdf B sdaf C weio A Adao B wowo C 2423 A - B 9 C C into: A asdfAdao- B sdafwowo9 C weio2423C ela wrote: > Is there any function in unix that can conveniently convert the followings > > > A asdf > B sdaf > C weio > > A Adao > B wowo > C 2423 > > A - > B 9 > C C > > > into: > > A asdfAdao- > B sdafwowo9 > C weio2423C > > How about your favorite text editor? Since we haven't the advantage of taking whatever course this is ho...

Sql to concatenate multiple rows to a single row / column
Is there a way of concatenating the results from one column / multiple rows into a one column / one row? Problem: I have a patient table with the following columns patid patname allergy table with the following columns patid allergy patid patname ============== 1 pat1 2 pat2 3 pat3 patid allergy ============= 1 allergy1 1 allergy2 .. .. 1 allergyn 2 allergy1 2 allergy2 .. .. .. how can I write a query to generate the following result patid ...

Re: Sql to concatenate multiple rows to a single row / column
SaltTan said: > > If your IDS version is more than 7 > You can do this with MULTISET Not entirely true. I tried this repeatedly with 8.50 and got absolutely nowhere. :o) -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien ` dire qu'il faut fermer sa gueule" - Coluche did i mention i like nulls? heck, i even go so far as to say that all columns in a table except the primary key could/should be nullable. this has certain advantages, for example, if you need to insert a child record and you don't have a parent row for it, just do ...

Multiple rows in table but display single row in select statement
Hello, I have 2 tables : Table A : a | b --------------- 1 | 2 1 | 4 2 | 6 3 | 7 3 | 8 3 | 9 Table B : b | c --------------- 2 | 2 2 | 4 4 | 6 4 | 6 4 | 7 6 | 8 6 | 9 Is it possible to doing this in SQL or PL/pgSQL? a | c ---------------------- 1 | 2, 4, 6 ,7 2 | 8, 9 Many thanks. ...

Elegant SQL placing multiple row columns in a single row column.
Dear All, wonder if anyone can help? A colleague constructed some SQL below and asked if there was a more elegant solution. As I'm an aged RPG programmer wasn't of much help I'm afraid. Anybody got a better solution? All input gratefully received. Best Regards Ian PS Apologies beforehand if this is simple and I'm being simple. *************************************************************************************************************** Hi Ian, Here was the messy solution... select distinct EAN, ADDNO, strip(r...

Re: Re: Re: Sql to concatenate multiple rows to a single row /
Amazing ... I didn't knew that there were a multiset to lvarchar cast. J. -----Original Message----- From: <curtiscrowson@bellsouth.net> To: Jean Sagi <jeansagi@myrealbox.com>,bozon <curtis@crowson1.com> Date: Thu, 17 Nov 2005 9:09:06 -0500 Subject: Re: Re: Sql to concatenate multiple rows to a single row / column The original poster of this SQL very cleverly cast the multiset to an lvarchar, note the ::lvarchar. I would never have thought of that. But once you do you have the string: MULTISET{"item1","item2","item3"}. Yo...

Re: Sql to concatenate multiple rows to a single row / column #4
What this exactly do ? Select patid, patname, replace(replace(substr(multiset (select item allergy from allergy where allergy.patid = patient.patid)::lvarchar,11),"'",""),"}","") from patient ; substr from a multiset ? J. bozon escribis: > Very cool. I was doing it the old fangled way. Nice to know that I can > always learn something. > > 8.5 is the Extended Parallel Server which is great for data warehouses > but hasn't really caught on. > > One quick note since you know that the string "MUL...

RE: Sql to concatenate multiple rows to a single row / column #2
Ok, I'm too curious. Does multiset work in IDS7.2? Rob -----Original Message----- From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org] On Behalf Of bozon Sent: Wednesday, November 16, 2005 10:55 AM To: informix-list@iiug.org Subject: Re: Sql to concatenate multiple rows to a single row / column Very cool. I was doing it the old fangled way. Nice to know that I can always learn something. 8.5 is the Extended Parallel Server which is great for data warehouses but hasn't really caught on. One quick note since you know that the string "MULTI...

RE: Sql to concatenate multiple rows to a single row / column #5
Konikoff, Rob \(Contractor\) said: > > Ok, I'm too curious. Does multiset work in IDS7.2? No. -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien ` dire qu'il faut fermer sa gueule" - Coluche did i mention i like nulls? heck, i even go so far as to say that all columns in a table except the primary key could/should be nullable. this has certain advantages, for example, if you need to insert a child record and you don't have a parent row for it, just do an insert into the parent table with the primary key value (everything else...

RE: Sql to concatenate multiple rows to a single row / column #3
Konikoff, Rob \(Contractor\) said: > > Ok, I'm too curious. Does multiset work in IDS7.2? No. -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien ` dire qu'il faut fermer sa gueule" - Coluche did i mention i like nulls? heck, i even go so far as to say that all columns in a table except the primary key could/should be nullable. this has certain advantages, for example, if you need to insert a child record and you don't have a parent row for it, just do an insert into the parent table with the primary key value (everything else...

Re: how to combine multiple excel files with multiple rows of
Probably easiest to bring them in with no names at all (depending on your DBMS option you'll have F1 F2 F3 or A B C as names) and then do a custom rename after they're brought in (make a name from row 3 and row 4, using RETAIN or LAG to combine the name together). -Joe On Mon, Mar 16, 2009 at 4:01 PM, Zibiao Zhang <zzhang@hsph.harvard.edu>wrote: > Dear all, > I have multiple excel files, each file has two spreadsheets. Four variables > are common throughout all the sheets, but may have different name. Each > sheet has summary on row 1 and 2, variable names are in...

how to combine multiple excel files with multiple rows of header.
Dear all, I have multiple excel files, each file has two spreadsheets. Four variables are common throughout all the sheets, but may have different name. Each sheet has summary on row 1 and 2, variable names are in row 3 and 4. Anybody knows a way to combine all the sheets of files into one sas datasets with only the four common variables? Thanks, STeven ...