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;
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
....
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.
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
....
@Tom You are right. How to tackle now? I'd to like to how to write proc transpose from proc sql join?
@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.
@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.
@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.
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: