topic Re: Join to Transpose in SAS Programming

Join to Transpose

Babloo — Wed, 16 Nov 2022 15:10:32 GMT

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;

Re: Join to Transpose

PaigeMiller — Wed, 16 Nov 2022 15:56:03 GMT

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.

Re: Join to Transpose

Tom — Wed, 16 Nov 2022 16:02:59 GMT

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

Re: Join to Transpose

Babloo — Wed, 16 Nov 2022 18:08:00 GMT

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

Re: Join to Transpose

Kurt_Bremser — Wed, 16 Nov 2022 22:14:21 GMT

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

Re: Join to Transpose

Babloo — Thu, 17 Nov 2022 14:43:57 GMT

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

Re: Join to Transpose

Tom — Thu, 17 Nov 2022 14:47:44 GMT

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

Re: Join to Transpose

PaigeMiller — Thu, 17 Nov 2022 17:57:56 GMT

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

Re: Join to Transpose

Kurt_Bremser — Thu, 17 Nov 2022 18:48:24 GMT

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.