Join to Transpose

[Babloo]

Can someone help me to convert the below join code to proc transpose? Whether it is efficient if we do so?

Currently I end up with I/O error due to many to many records after joins.

proc sql;
Create table tst as
SELECT DISTINCT LEVEL_A,LEVEL_B,PT_NAME,BU, COM_CATG,CATG, PLN_TO, POSTNG_YR FROM WORK.FINAL_SUB t1
LEFT JOIN (   SELECT BU, CATG, PLN_TO,  NEW_GSV, NEW_TRADE
      FROM WORK.FINAL_SUB WHERE POSTNG_PED="1" ) per1
ON t1.BU=per1.BU AND t1.CATG=per1.CATG
AND t1.PLN_TO=per1.PLN_TO 
LEFT JOIN (   SELECT BU, CATG, PLN_TO,  NEW_GSV, NEW_TRADE
      FROM WORK.FINAL_SUB WHERE POSTNG_PED="2" ) per2
ON t1.BU=per2.BU AND t1.CATG=per2.CATG
AND t1.PLN_TO=per2.PLN_TO
LEFT JOIN (   SELECT BU, CATG, PLN_TO,  NEW_GSV, NEW_TRADE
      FROM WORK.FINAL_SUB WHERE POSTNG_PED="3" ) per3
ON t1.BU=per3.BU AND t1.CATG=per3.CATG
AND t1.PLN_TO=per3.PLN_TO
LEFT JOIN (   SELECT BU, CATG, PLN_TO,  NEW_GSV, NEW_TRADE
      FROM WORK.FINAL_SUB WHERE POSTNG_PED="4" ) per4
ON t1.BU=per4.BU AND t1.CATG=per4.CATG
AND t1.PLN_TO=per4.PLN_TO
LEFT JOIN (   SELECT BU, CATG, PLN_TO,  NEW_GSV, NEW_TRADE
      FROM WORK.FINAL_SUB WHERE POSTNG_PED="5" ) per5
ON t1.BU=per5.BU AND t1.CATG=per5.CATG
AND t1.PLN_TO=per5.PLN_TO
LEFT JOIN (   SELECT BU, CATG, PLN_TO,  NEW_GSV, NEW_TRADE
      FROM WORK.FINAL_SUB WHERE POSTNG_PED="6" ) per6
ON t1.BU=per6.BU AND t1.CATG=per6.CATG
AND t1.PLN_TO=per6.PLN_TO
LEFT JOIN (   SELECT BU, CATG, PLN_TO,  NEW_GSV, NEW_TRADE
      FROM WORK.FINAL_SUB WHERE POSTNG_PED="7" ) per7
ON t1.BU=per7.BU AND t1.CATG=per7.CATG
AND t1.PLN_TO=per7.PLN_TO
LEFT JOIN (   SELECT BU, CATG, PLN_TO,  NEW_GSV, NEW_TRADE
      FROM WORK.FINAL_SUB WHERE POSTNG_PED="8" ) per8
ON t1.BU=per8.BU AND t1.CATG=per8.CATG
AND t1.PLN_TO=per8.PLN_TO
LEFT JOIN (   SELECT BU, CATG, PLN_TO,  NEW_GSV, NEW_TRADE
      FROM WORK.FINAL_SUB WHERE POSTNG_PED="9" ) per9
ON t1.BU=per9.BU AND t1.CATG=per9.CATG
AND t1.PLN_TO=per9.PLN_TO
LEFT JOIN (   SELECT BU, CATG, PLN_TO,  NEW_GSV, NEW_TRADE
      FROM WORK.FINAL_SUB WHERE POSTNG_PED="10" ) per10
ON t1.BU=per10.BU AND t1.CATG=per10.CATG
AND t1.PLN_TO=per10.PLN_TO
LEFT JOIN (   SELECT BU, CATG, PLN_TO,  NEW_GSV, NEW_TRADE
      FROM WORK.FINAL_SUB WHERE POSTNG_PED="11" ) per11
ON t1.BU=per11.BU AND t1.CATG=per11.CATG
AND t1.PLN_TO=per11.PLN_TO
LEFT JOIN (   SELECT BU, CATG, PLN_TO,  NEW_GSV, NEW_TRADE
      FROM WORK.FINAL_SUB WHERE POSTNG_PED="12" ) per12
ON t1.BU=per12.BU AND t1.CATG=per12.CATG
AND t1.PLN_TO=per12.PLN_TO
LEFT JOIN (   SELECT BU, CATG, PLN_TO,  TEMP_GSV, TEMP_KMF
      FROM WORK.FINAL_SUB WHERE POSTNG_PED="&cperiod" ) submitted
ON t1.BU=submitted.BU AND t1.CATG=submitted.CATG
AND t1.PLN_TO=submitted.PLN_TO

;
quit;

[PaigeMiller]

I have to believe that TRANSPOSE will be more efficient than SQL. And it will likely take less time to program, as well.

As far as trying to replicate your SQL code in TRANSPOSE, can you provide a portion of the data set (as working SAS data step code, and not as Excel or screen capture or any other method), and also provide the desired output? But better yet, before you do that, try TRANSPOSE yourself and see if you can get it to work.

[Tom · Accepted solution]

The code does not make much sense.  If is left joining but not taking any variables from the additional tables it is joining with.

Did you instead mean to use SQL like this instead?

SELECT t1.BU
     , t1.CATG
     , t1.PLN_TO
     , per1.new_gsv as GSV1 
     , per2.new_gsv as GSV2 
FROM WORK.FINAL_SUB t1
LEFT JOIN 
(SELECT BU, CATG, PLN_TO,  NEW_GSV, NEW_TRADE 
 FROM WORK.FINAL_SUB WHERE POSTNG_PED="1" ) per1
  ON t1.BU=per1.BU AND t1.CATG=per1.CATG
  AND t1.PLN_TO=per1.PLN_TO 
LEFT JOIN (   SELECT BU, CATG, PLN_TO,  NEW_GSV, NEW_TRADE
      FROM WORK.FINAL_SUB WHERE POSTNG_PED="2" ) per2
ON t1.BU=per2.BU AND t1.CATG=per2.CATG
AND t1.PLN_TO=per2.PLN_TO
....

[Babloo]

@Tom You are right. How to tackle now? I'd to like to how to write proc transpose from proc sql join?

[Kurt_Bremser]

---

@Babloo wrote:

@Tom You are right. How to tackle now? I'd to like to how to write proc transpose from proc sql join?

---

Post example data, as already requested, and the expected result.

[Babloo]

@Kurt_Bremser @Tom Currently I do not have any data or excepted results to share. Only thing which I want to know is how to transpose the proc sql with multiple left joins as shown in the post.

[Tom]

---

@Babloo wrote:

@Kurt_Bremser @Tom Currently I do not have any data or excepted results to share. Only thing which I want to know is how to transpose the proc sql with multiple left joins as shown in the post.

---

The SQL in the original post is gibberish.  It does nothing useful.  So it is hard to figure out how to convert it to anything

It kind of looks like an attempt to do a transpose, but the list of variables selected (the list between SELECT and first LEFT JOIN keyword) does not reference any of the variables from the joined in datasets.  So it is impossible to tell what variable(s) you are trying to transpose.

[PaigeMiller]

---

@Babloo wrote:

@Kurt_Bremser @Tom Currently I do not have any data or excepted results to share. Only thing which I want to know is how to transpose the proc sql with multiple left joins as shown in the post.

---

But you know what you want to do. Otherwise you wouldn't be asking. Explain it. Make up some data. Make up the desired output. If you can't communicate what you want to do and show us, we can't help you.

[Kurt_Bremser]

You know what GIGO stands for? It means "garbage in, garbage out".

Since your SQL code achieves nothing in the way of transposing, it's garbage, so we coukd only give you other garbage.

Post usable example (fake) data, and the expected result. If you can't provide this, then we're talking about a non-issue.