Merging one variable in a column of a table with all the values in a different column of another.

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Merging one variable in a column of a table with all the values in a different column of another.

I have this dilemma, I tried to do this in excel, but with the sheer amount of data its going to take forever and I have not found any sort of method in excel to perfrom a loop of some sort. 

 

Basically I have these 5 levels as each level has an addition of code. Bh basically take EBRDBG01 and merge that with all the variables in column PH4, then do the same with EBRDBG02...etc. This same basically needsto be done with level 3, level 4.

 

Level 3 is easy to compose since Level 2 only has one variable in the table. However Level 3 and Level 4 have several variables in the table and each one needs to be merged with the contents of the PH columns. 

 

Is this even possible in SAS? I appreciate any advice or pointers, thanks guys!

 

Also please let me know if you need more clarification, I do apologize for the codes, they tend to get confusing looking as they grow bigger. 

 

 

 

Level 2
EBRD

 

       +

 

PH3
BG01
BG02
BG03
BG04
BG05
BG06
BG07
BG08
BG09
BG10

 

     =

 

Level 3
EBRDBG01
EBRDBG02
EBRDBG03
EBRDBG04
EBRDBG05
EBRDBG06
EBRDBG07
EBRDBG08
EBRDBG09
EBRDBG10

 

         +

PH4
BBR01
BBR02
BBR03
BBR04
BBR05
BBR06
BBR07
BBR08
BBR09
BBR10

 

   =  

 

Level 4
EBRDBG01BBR01
EBRDBG01BBR02
EBRDBG01BBR03
EBRDBG01BBR04
EBRDBG01BBR05
EBRDBG01BBR06
EBRDBG01BBR07
EBRDBG01BBR08
EBRDBG01BBR09
EBRDBG01BBR10
EBRDBG02BBR01
EBRDBG02BBR02
EBRDBG02BBR03
EBRDBG02BBR04
EBRDBG02BBR05
EBRDBG02BBR06
EBRDBG02BBR07
EBRDBG02BBR08
EBRDBG02BBR09
EBRDBG02BBR10
EBRDBG03BBR01
EBRDBG03BBR02
EBRDBG03BBR03
EBRDBG03BBR04
EBRDBG03BBR05
EBRDBG03BBR06
EBRDBG03BBR07
EBRDBG03BBR08
EBRDBG03BBR09
EBRDBG03BBR10

This continues for EBRDG04, 05, 06, etc. 

        +

 

PH5
BF001
BF002
BF003
BF004
BF005
BF006
BF007
BF008
BF009
BF010
BF011
BF012
BF013
BF014
BF015
BF016
BF017

 

 

This continues for EBRDBG04..05...06...etc.


Accepted Solutions
Solution
‎06-20-2017 12:28 PM
Super User
Posts: 5,369

Re: Merging one variable in a column of a table with all the values in a different column of another

For all combinations, SQL is usually the right tool for the job.  Here's the approach:

 

proc sql;

create table want as

select strip(ph1) || strip(ph2) || strip(ph3) || strip(ph4) || strip(ph5) as combo

from level1, level2, level3, level4, level5;

quit;

 

I'm making assumptions about the names of the fields, the names of the incoming data sets, whether you want to strip out blanks, and you may want to set a length for the resulting field.  But this should be a viable approach.

View solution in original post


All Replies
Solution
‎06-20-2017 12:28 PM
Super User
Posts: 5,369

Re: Merging one variable in a column of a table with all the values in a different column of another

For all combinations, SQL is usually the right tool for the job.  Here's the approach:

 

proc sql;

create table want as

select strip(ph1) || strip(ph2) || strip(ph3) || strip(ph4) || strip(ph5) as combo

from level1, level2, level3, level4, level5;

quit;

 

I'm making assumptions about the names of the fields, the names of the incoming data sets, whether you want to strip out blanks, and you may want to set a length for the resulting field.  But this should be a viable approach.

Occasional Contributor
Posts: 6

Re: Merging one variable in a column of a table with all the values in a different column of another

I am a bit new to sas, so would I import the excel file into SAS and run the proc sql command that you have provided? or would I import each column Level 2, PH3, Level 3, PH4, Level 4, PH5 as seperate tables?

 

Thanks for the help, I really appreciate it!

Super User
Posts: 5,369

Re: Merging one variable in a column of a table with all the values in a different column of another

[ Edited ]

They way I pictured it, you would import 5 separate files into SAS:

 

The file named Level1 would contain the variable PH1, the file named Level2 would contain the variable PH2, etc.

 

That's the easiest way to allow each variable to have a different number of unique values.

 

The program could be adapted to handle other data structures, but that's the way it is designed for now.

Occasional Contributor
Posts: 6

Re: Merging one variable in a column of a table with all the values in a different column of another

Thanks so much for the help! I got exactly what I needed.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 136 views
  • 0 likes
  • 2 in conversation