Good morning,
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.
My goal is to create an additional column, called "COMBINAISON", where, for each row, only the name(s) of non-zero columns would appear.
For example : on first row, if all 6 fields are equal to zero, then return "" on first row.
On second row, if only C2 and C5 respect the condition "> 0", then return "C2, C5" on second row.
On third row, if only C3 > 0, then return "C3" on third row.
And so on...
Here is what I have reached. No syntax error, but the query runs for a very long time.
Thank you for your help.
/* 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
) )
;
Is this what you are trying to do?
data have ;
input c1-c6 ;
cards ;
0 0 0 0 0 0
0 0 1 0 0 0
0 1 0 0 1 0
;
data want;
set have ;
length result $200 ;
array c c1-c6 ;
do index=1 to dim(c);
if c[index] then result=catx(',',result,vname(c[index]));
end;
drop index;
run;
Result:
Obs c1 c2 c3 c4 c5 c6 result 1 0 0 0 0 0 0 2 0 0 1 0 0 0 c3 3 0 1 0 0 1 0 c2,c5
Is there any reason you need to use SQL for this?
proc sql;
create table want as
select *
, catx(','
, case when c1 then "c1" else ' ' end
, case when c2 then "c2" else ' ' end
, case when c3 then "c3" else ' ' end
, case when c4 then "c4" else ' ' end
, case when c5 then "c5" else ' ' end
, case when c6 then "c6" else ' ' end
) as result length=200
from have
;
quit;
@Dev2021 Can I suggest that you post sample data, and the expected output. The more help you provide the faster you are likely to get a helpful response.
Here's what I think you are looking for. Note - This code will ONLY work for 6 variables without modification.
/* Create test data */
data have ;
infile cards ;
rowNumber+1 ;
input c1 c2 c3 c4 c5 c6 ;
cards ;
0 0 0 0 0 0
0 0 1 0 0 0
0 1 0 0 1 0
;
run ;
data want ;
/* Read data */
set have ;
/* Create output variable */
length COMBINAISON $18 ;
/* Array to reference c1-c6 */
array c{6} ;
/* Clear COMBINAISON */
COMBINAISON="" ;
/* Loop through array elements */
do i=1 to 6 ;
/* If the value of c{n}>1 then we need to capture that in COMBINAISON */
if c{i}>0 then
do ;
/* Add a comma between values in COMBINAISON
only if there is a value in COMBINAISON already */
if COMBINAISON ne "" then
COMBINAISON=trim(COMBINAISON)!!"," ;
/* Add the variable to COMBINAISON */
COMBINAISON=trim(COMBINAISON)!!"C"||left(putn(i,"8.")) ;
end ;
end ;
/* Output */
output ;
run ;
This should work for an arbitrary number of variables, assuming they are named c1 c2 c3 ...
data want ;
/* Read data */
set have ;
/* Create output variable */
length result $200 ;
/* Array to reference c1-c6 */
array c c: ;
/* Clear COMBINAISON */
call missing(result);
/* Loop through array elements */
do i=1 to dim(c) ;
/* If the value of c{n}>1 then we need to capture that in COMBINAISON */
if c{i}>0 then
do ;
/* Add a comma between values in COMBINAISON
only if there is a value in COMBINAISON already */
if RESULT ne "" then
RESULT=trim(RESULT)!!"," ;
/* Add the variable to COMBINAISON */
RESULT=trim(RESULT)!!"C"||left(putn(i,"8.")) ;
end ;
end ;
/* Output */
output ;
drop i;
run ;
I changed variable name from COMBINAISON to RESULT so that using c: in the ARRAY statement would work, it won't work if we use any variable name like COMBINAISON that begins with the letter C.
To @Dev2021 ... why are you doing this? What benefit is there to have "C2,C5" compared to just having numeric variables with 0 or 1? I don't see any benefit, I just see extra work.
Submit for your consideration: "Different from zero" is not the same as "> 0" used in your code.
Perhaps you are assuming you never have negative values but the general phrasing of your question subject does not agree with attempted code.
Note that the several proposed solutions do not use SQL. Quite often doing the same thing with multiple variables means that an ARRAY, which is not available in SQL, will be an easier to code and quite often faster executing solution than in SQL.
Is this what you are trying to do?
data have ;
input c1-c6 ;
cards ;
0 0 0 0 0 0
0 0 1 0 0 0
0 1 0 0 1 0
;
data want;
set have ;
length result $200 ;
array c c1-c6 ;
do index=1 to dim(c);
if c[index] then result=catx(',',result,vname(c[index]));
end;
drop index;
run;
Result:
Obs c1 c2 c3 c4 c5 c6 result 1 0 0 0 0 0 0 2 0 0 1 0 0 0 c3 3 0 1 0 0 1 0 c2,c5
Is there any reason you need to use SQL for this?
proc sql;
create table want as
select *
, catx(','
, case when c1 then "c1" else ' ' end
, case when c2 then "c2" else ' ' end
, case when c3 then "c3" else ' ' end
, case when c4 then "c4" else ' ' end
, case when c5 then "c5" else ' ' end
, case when c6 then "c6" else ' ' end
) as result length=200
from have
;
quit;
Good afternoon,
I'll try to answer to all of you.
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).
By the way, there is no specific reason for SQL, I just do not know how to use SAS (excepted for a few procedures).
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.
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).
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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: