SAS SQL Join without overwriting the variable

[SASdevAnneMarie]

Hello Experts,

I am wondering how to join with sas sql the tables without overwriting the variables.

For example,  I would like to join this data, but in output I would like to have the Value_1=50, Value_2=50.

Key	Value
PI23X	50
PI23X	50

Thank you foryour help !

[PaigeMiller]

Please give a more complete question. Show us a data set with multiple values of KEY. Show us a data set with multiple values of VALUE. Show us an example of a data set to join with. Show us the desired output as a table, not words.

Also, why SQL here? If we can get the result a different way without SQL, is that acceptable?

You have been in this forum for a long time, and we have helped you with many problems. You have to help us as well, by posting data as working SAS data step code (examples and instructions), and not in other formats. Please do that in your answer to my questions above.

[Tom · Accepted solution]

It would be very difficult to do that with pure SQL code since you do not have anything in the data itself to indicate which value should go to which new variable.  This is because SQL is a set operation language.  So it does not know or care about the order of the observations. 

If is simple in SAS do this.  Use PROC TRANSPOSE.

proc transpose data=have out=want(drop=_name_) prefix=Value_;
  by key;
  var value;
run;

To do it with SQL you will first need to create a new variable so you have something to reference in the code.

data for_sql;
  set have;
  by key;
  rowno+1;
  if first.key then rowno=1;
run;

Now it might be possible to use a JOIN .

proc sql;
create table want as 
select * 
from (select key,value as value_1 from for_sql where rowno=1)
natural join (select key,value as value_2 from for_asql where rowno=2)
;
quit;

[SASdevAnneMarie]

Thank you, Tom!

[ballardw]

---

@SASdevAnneMarie wrote:

Hello Experts,

I am wondering how to join with sas sql the tables without overwriting the variables.

For example,  I would like to join this data, but in output I would like to have the Value_1=50, Value_2=50.

Key	Value
PI23X	50
PI23X	50

Thank you foryour help !

---

In addition, it would be a good idea to show the code that is generating this "overwrite" of the variable. I am actually suspecting what  you are seeing is duplication of key with different values of "value".

It really isn't clear if "Value_1=50, Value_2=50" means that you have two variable on an observation with different values or not.

[SASdevAnneMarie]

Hello,

Thank you for your message.
I have different values, for example, Value_1=50, Value_2=70