topic Re: Match Columns data in SAS Programming

Match Columns data

kumarK — Wed, 19 Aug 2015 18:29:05 GMT

Hi,

I have two data sets like this,

data t1;

input id$;

cards;

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...

Re: Match Columns data

PGStats — Wed, 19 Aug 2015 18:38:52 GMT

Can you explain what you want, in words?

Re: Match Columns data

Steelers_In_DC — Wed, 19 Aug 2015 18:46:28 GMT

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;

Re: Match Columns data

Haikuo — Wed, 19 Aug 2015 18:56:07 GMT

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

Re: Match Columns data

kumarK — Wed, 19 Aug 2015 19:02:56 GMT

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.

Re: Match Columns data

kumarK — Wed, 19 Aug 2015 19:08:11 GMT

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

Re: Match Columns data

kumarK — Wed, 19 Aug 2015 19:10:51 GMT

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..

Re: Match Columns data

Haikuo — Wed, 19 Aug 2015 19:34:26 GMT

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.

Re: Match Columns data

Steelers_In_DC — Wed, 19 Aug 2015 19:36:12 GMT

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;

;

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;

Re: Match Columns data

PGStats — Wed, 19 Aug 2015 22:17:49 GMT

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;

Re: Match Columns data

kumarK — Thu, 20 Aug 2015 04:42:00 GMT

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.

Re: Match Columns data

kumarK — Thu, 20 Aug 2015 17:49:29 GMT

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..?

Re: Match Columns data

kumarK — Thu, 20 Aug 2015 17:51:17 GMT

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?

Re: Match Columns data

PGStats — Thu, 20 Aug 2015 18:21:29 GMT

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.