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...
Can you explain what you want, in words?
PG
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.
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;
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 |
Homework? Please show us what you have tried, or at least some thoughts?
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..
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.
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.
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;
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?
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
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..?
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.