topic Re: SAS SQL Join without overwriting the variable in SAS Programming

SAS SQL Join without overwriting the variable

SASdevAnneMarie — Tue, 29 Apr 2025 15:51:39 GMT

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 !

Re: SAS SQL Join without overwriting the variable

PaigeMiller — Tue, 29 Apr 2025 16:24:19 GMT

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.

Re: SAS SQL Join without overwriting the variable

Tom — Tue, 29 Apr 2025 16:38:42 GMT

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;

Re: SAS SQL Join without overwriting the variable

ballardw — Tue, 29 Apr 2025 20:35:12 GMT

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

Re: SAS SQL Join without overwriting the variable

SASdevAnneMarie — Thu, 01 May 2025 17:56:53 GMT

Hello,

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

Re: SAS SQL Join without overwriting the variable

SASdevAnneMarie — Thu, 01 May 2025 18:44:34 GMT

Thank you, Tom!