Dev2021 Tracker
https://communities.sas.com/kntur85557/tracker
Dev2021 TrackerMon, 15 Jul 2024 11:09:34 GMT2024-07-15T11:09:34ZRe: Proc sql : Row value equals column name(s), when different from zero
https://communities.sas.com/t5/SAS-Programming/Proc-sql-Row-value-equals-column-name-s-when-different-from-zero/m-p/773956#M245929
<P>Good afternoon,</P><P> </P><P>I'll try to answer to all of you.</P><P> </P><P>1) I have found a solution earlier. I just needed to make some calculations to check if it made sense. Actually I ended up using a similar technique as Tom (the proc sql one), and then if needed I can use a left join (avoiding to update allows me to also avoid the subquery, which took so long).<BR />By the way, there is no specific reason for SQL, I just do not know how to use SAS (excepted for a few procedures).</P><P> </P><P>2) For AMSAS and PaigeMiller : thank you too for your help. Actually I'm not used to post questions, therefore I thought it was clear that C1... C6 meant "column number 1, 2, ... 6" (but have different names). Sorry if it was not clear, I will try to be more explicite in the future.</P><P> </P><P>3) for ballardw : thank you for those precisions. Yes as you guessed, no negative values possible in my case (however, for a more general case, it would be dangerous).</P>Wed, 13 Oct 2021 15:15:41 GMThttps://communities.sas.com/t5/SAS-Programming/Proc-sql-Row-value-equals-column-name-s-when-different-from-zero/m-p/773956#M245929Dev20212021-10-13T15:15:41ZProc sql : Row value equals column name(s), when different from zero
https://communities.sas.com/t5/SAS-Programming/Proc-sql-Row-value-equals-column-name-s-when-different-from-zero/m-p/773855#M245890
<P>Good morning,</P><P> </P><P>Here is my question : I have a table "MyTable", and among available columns, I have : A) a "RowNumber" field. Its function is obvious. B) 6 numeric fields, let's call them C1, C2, .... C6.</P><P>My goal is to create an additional column, called "COMBINAISON", where, for each row, only the name(s) of non-zero columns would appear.</P><P>For example : on first row, if all 6 fields are equal to zero, then return "" on first row.</P><P>On second row, if only C2 and C5 respect the condition "> 0", then return "C2, C5" on second row.</P><P>On third row, if only C3 > 0, then return "C3" on third row.</P><P>And so on...</P><P> </P><P>Here is what I have reached. No syntax error, but the query runs for a very long time.<BR />Thank you for your help.</P><P> </P><PRE><CODE class="">/* We create a copy, as the table can't
update itself : */
create table COPIE_DE_LA_TABLE
AS select *
FROM MyTable;
/* "ALTER + UPDATE" in order to
define combinations : */
ALTER TABLE MaTable
ADD COMBINAISON CHAR(100);
UPDATE MyTable
SET COMBINAISON =
(SELECT
CASE WHEN LENGTH(liste) > 0
THEN SUBSTR(liste, 1,
LENGTH(liste) - 1)
ELSE ''
END
FROM ( select
CASE WHEN C1 > 0
THEN 'C1,' ELSE '' END
|| CASE WHEN C2 > 0
THEN 'C2,' ELSE '' END
|| CASE WHEN C3 > 0
THEN 'C3,' ELSE '' END
|| CASE WHEN C4 > 0
THEN 'C4,' ELSE '' END
|| CASE WHEN C5 > 0
THEN 'C5,' ELSE '' END
|| CASE WHEN C6 > 0
THEN 'C6,' ELSE '' END
AS liste
/* And here comes the moment to
use the copy : */
FROM COPIE_DE_LA_TABLE
WHERE MyTable.RowNumber
= COPIE_DE_LA_TABLE.RowNumber
) )
;</CODE></PRE><P> </P><P> </P>Wed, 13 Oct 2021 08:11:08 GMThttps://communities.sas.com/t5/SAS-Programming/Proc-sql-Row-value-equals-column-name-s-when-different-from-zero/m-p/773855#M245890Dev20212021-10-13T08:11:08Z