Match Columns data

[kumarK]

Hi,

I have two data sets like this,

data t1;

input id$;

cards;

a

b

c

d

run;

I have second table has 100 of columns like below:

id1	val1	id2	val2	id3	val3
a	200	b	10	a	20.22
b	300	c	30	c	2.12
c	400	d	20
d	100

I want output like below All the id's of "a" values in one row and all the id's of  "b" values in another row like that: Please help me the code in SQL so that i can tweak as per requirement.

id	val1	val2	val3	val4	val5	val6	val7	val8	val9
a	200	.	20.22
b	300	10	.
c	400	30	2.12
d	100	20	.

Thanks alot...

[PGStats]

Can you explain what you want, in words?

PG

[kumarK]

I want id wise observations , in output data set i want for id "a" row only i want "a" observations, for id "b" row i want "b" observations like that.. if there any missing values simply put null in those columns..i hope you understand the question.

[Steelers_In_DC]

I think this is what you want but what you have as your output doesn't match what this code will do.  If my output is wrong please explain why you have a blank for val2 in row 1 and 10 for val2 in row 2:

proc sql;

create table want as

select

a.*,

b.val1,

b.val2,

b.val3

from t1 a left join

     t2 b on

a.id = b.id1;

[kumarK]

Thanks for the reply. The code is giving incorrect results. Answer to your question - Actually i have missing observations for those columns.

below my second table.. first row 3rd column id "a" missing it occupied by id "b"  like that i have the data.

id1	val1	id2	val2	id3	val3
a	200	b	10	a	20.22
b	300	c	30	c	2.12
c	400	d	20
d	100

[Haikuo]

Homework? Please show us what you have tried, or at least some thoughts?

[kumarK]

Not homework.. I have faced this scenario in my work.

i thought first arrays then i thought some match merging but no idea how to proceed.

Thanks..

[Haikuo]

Then why it has to be SQL?

The reason I asked this is that , Even though it can done using data step, but SQL is obviously the text book answer.

[kumarK]

The reason is, i have to run some codes on the SQL Server.  if i get an idea about the logic then i can tweak as per my requirement. Thanks lot for suggestions.

[Steelers_In_DC]

That was my question too, if you have tried arrays why does it need to be in sql?  I'm curious to see another solution, but here is one.  Doesn't look that good to me but it gets the results:

data t1;

input id$;

cards;

a

b

c

d

;

data t2;

infile cards missover;

input id1$ val1  id2$ val2 id3$ val3;

cards;

a 200   b   10   a   20.22

b 300   c   30   c    2.12

c 400   d   20

d 100

;

proc sql;

create table one as

select a.id,b.val1

from t1 a left join

     t2 b on

a.id = b.id1;

create table two as

select a.*,b.val2

from one a left join

     t2 b on

a.id = b.id2;

create table want as

select a.*,b.val3

from two a left join

     t2 b on

a.id = b.id3;

[kumarK]

Thanks Mark.

As i edited my question please have a look, the code you given is for some columns, i have many columns, Is there any other way?

[PGStats]

I think the simplest solution would be:

proc sql;

select

a.id1, a.val1, b.val2 as val2, c.val3 as val3

from

    t2 as a left join

    t2 as b on a.id1=b.id2 left join

    t2 as c on a.id1=c.id3;

quit;

PG

[kumarK]

Thanks PGStats. It is very helpful for some columns.

Actually i have lots of columns, so i have to mention all the column names kn the code, is there any other way to do it similar..?

[PGStats]

If those extra columns are not involved in the reshaping issue, you could do:

proc sql;

select

a.*, b.val2 as newVal2, c.val3 as newVal3

from

    t2 as a left join

    t2 as b on a.id1=b.id2 left join

    t2 as c on a.id1=c.id3;

quit;

otherwise, yes, you have to do some extra typing.   ... or... start a new discussion about solving this with macro programming.

PG