DATA Step, Macro, Functions and more

Match Columns data

Reply
Frequent Contributor
Posts: 84

Match Columns data

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:

id1val1id2val2id3val3
a200b10a20.22
b300c30c2.12
c400d20
d100

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.

idval1val2val3val4val5val6val7val8val9
a200.20.22
b30010.
c400302.12
d10020.

Thanks alot...

Respected Advisor
Posts: 4,654

Re: Match Columns data

Can you explain what you want, in words?

PG

PG
Frequent Contributor
Posts: 84

Re: Match Columns data

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.

Valued Guide
Posts: 858

Re: Match Columns data

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;

Frequent Contributor
Posts: 84

Re: Match Columns data

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.

id1val1id2val2id3val3
a200b10a20.22
b300c30c2.12
c400d20
d100
Respected Advisor
Posts: 3,124

Re: Match Columns data

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

Frequent Contributor
Posts: 84

Re: Match Columns data

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

Respected Advisor
Posts: 3,124

Re: Match Columns data

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.

Frequent Contributor
Posts: 84

Re: Match Columns data

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.

Valued Guide
Posts: 858

Re: Match Columns data

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;

Frequent Contributor
Posts: 84

Re: Match Columns data

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?

Respected Advisor
Posts: 4,654

Re: Match Columns data

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

PG
Frequent Contributor
Posts: 84

Re: Match Columns data

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

Respected Advisor
Posts: 4,654

Re: Match Columns data

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. Smiley Happy  ... or... start a new discussion about solving this with macro programming.

PG

PG
Ask a Question
Discussion stats
  • 13 replies
  • 368 views
  • 9 likes
  • 4 in conversation