Proc sql : Row value equals column name(s), when different from zero

[Dev2021]

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
        )	)
;

[AMSAS]

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

Data2DataStep Maco instructions

[AMSAS]

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 ;
		

[PaigeMiller]

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.

[ballardw]

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.

[Tom · Accepted solution]

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]

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