BookmarkSubscribeRSS Feed
kumarK
Quartz | Level 8

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

13 REPLIES 13
PGStats
Opal | Level 21

Can you explain what you want, in words?

PG

PG
kumarK
Quartz | Level 8

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
Barite | Level 11

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
Quartz | Level 8

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
Haikuo
Onyx | Level 15

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

kumarK
Quartz | Level 8

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

Haikuo
Onyx | Level 15

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
Quartz | Level 8

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
Barite | Level 11

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
Quartz | Level 8

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
Opal | Level 21

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
kumarK
Quartz | Level 8

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
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 1401 views
  • 9 likes
  • 4 in conversation