topic Re: Concatenate columns names when value=1 in SAS Programming

Concatenate columns names when value=1

Ronein — Sun, 08 Aug 2021 06:31:01 GMT

Hello

What is the way to concatenate all columns names when value=1?

I want to do it only for columns that start with name "Ind".

I want to have a dynamic program because the number of columns (columns start with Ind) might be changed.

In this example the wanted new column called "New" will get the following values:

For ID=1 the value will be 315,527

For ID=2 the value will be 118,315

For ID=3 the value will be 118,224,315,527

For ID=4 the value will be 527

I also want to create another new columns called "New2" that will have the concatenate descriptions (using proc format).

proc format ;

value fff

118='Reason_A'

224='Reason_B'

315='Reason_C'

527='Reason_D'

;

Run;

For ID=1 the value will be 'Reason_C,Reason_D'
For ID=2 the value will be 'Reason_A,Reason_C'
For ID=3 the value will be 'Reason_A,Reason_B,Reason_C,Reason_D'
For ID=4 the value will be ' Reason_D'

Data have;
input ID Ind118 Ind224 Ind315 Ind527 ;
cards;
1 0 0 1 1 
2 1 0 1 0
3 1 1 1 1 
4 0 1 0 0
5 0 0 0 1
;
Run;

Re: Concatenate columns names when value=1

Kurt_Bremser — Sun, 08 Aug 2021 07:23:30 GMT

First, get data out of structure by transposing, and eliminate the 0 values:

proc transpose
  data=have
  out=long1 (where=(col1 ne 0))
;
by id;
var ind:;
run;

Next get the value out of _name_, and drop the now redundant col1:

data long2;
set long;
length ind $3;
ind = substr(_name_,4);
drop _name_ col1;
run;

Then concatenate:

data wanr;
set long2;
by id;
length value $100;
if first.id then value = "";
value = catx(',',value,put(ind,$fff.));
if last.id;
keep id value;
run;

Untested, posted from my tablet.

PS your format needs to be a character format.

Re: Concatenate columns names when value=1

Tom — Sun, 08 Aug 2021 15:37:28 GMT

Don't store the REASON into the NAME of the variable. Store it as its own variable.

data tall;
  input ID @ ;
  do reason=118,224,315,527 ;
    input value @;
    output;
  end;
cards;
1 0 0 1 1 
2 1 0 1 0
3 1 1 1 1 
4 0 1 0 0
5 0 0 0 1
;

Then the problem is simple.

proc format ;
  value fff
    118='Reason_A'
    224='Reason_B'
    315='Reason_C'
    527='Reason_D'
  ;
run;

data want;
  do until(last.id);
    set tall ;
    by id;
    length new new2 $200;
    if value then do;
       new=catx(',',new,reason);
       new2=catx(',',new2,put(reason,fff.));
    end;
  end;
  drop reason value;
run;

Results

Obs    ID    new                new2

 1      1    315,527            Reason_C,Reason_D
 2      2    118,315            Reason_A,Reason_C
 3      3    118,224,315,527    Reason_A,Reason_B,Reason_C,Reason_D
 4      4    224                Reason_B
 5      5    527                Reason_D

If you keep it in the "wide" format then you will need convert the text from the name into the numeric reason to use the format.

data want;
  set have;
  array ind ind:;
  length new new2 $200;
  do index=1 to dim(ind);
    if ind[index] then do;
      reason=input(substr(vname(ind[index]),4),32.);
      new=catx(',',new,reason);
      new2=catx(',',new2,put(reason,fff.));
    end;
  end;
  drop index reason;
run;

Re: Concatenate columns names when value=1

mkeintz — Sun, 08 Aug 2021 19:23:27 GMT

I think the key issue here, is the extracting the numeric part of the variable name - i.e. the numeric characters following IND in the variable name. You can use the VNAME function to save those characters in a retained array, which you need to do only once, during the first observation.

Then just retrieve those characters when the corresponding IND variable =1.

data have;
input ID Ind118 Ind224 Ind315 Ind527 ;
cards;
1 0 0 1 1 
2 1 0 1 0
3 1 1 1 1 
4 0 1 0 0
5 0 0 0 1
run;

proc format;
  value $fff
    '118'='Reason_A'
    '224'='Reason_B'
    '315'='Reason_C'
    '527'='Reason_D'
    '999'='Other Reason'
    ;
run;

data want (drop=_:);
  set have;
  array _vals {10} $3 ;
  retain _vals: ; /*Numeric characters from the IND varnames */
  array _ind ind: ;
  if _n_=1 then do over _ind;
    _vals{_i_} = left(tranwrd(upcase(vname(_ind)),'IND',''));
  end;
  length codes $15 ;
  length reason $100;
  do over _ind;
    if _ind=1 then do;
      codes=catx(',',codes,_vals{_i_});
      reason=catx(',',reason,put(_vals{_i_},$fff.));
    end;
  end;
run;

Be sure to make the _VALS array large enough to accommodate the maximum number of expected IND variables.

Re: Concatenate columns names when value=1

Tom — Sun, 08 Aug 2021 22:03:43 GMT

If you are going to use implicit array reference then why not go all in and use it everywhere.

(Also why not use the numeric format the original poster created instead of having to make a new character format?).

proc format;
  value fff
    118='Reason_A'
    224='Reason_B'
    315='Reason_C'
    527='Reason_D'
    999='Other Reason'
  ;
run;

data want ;
  set have;
  array reason reason1-reason100;
  retain reason: ;
  array ind ind: ;
  if _n_=1 then do over ind;
    reason = input(substr(vname(ind),4),32.);
  end;
  length new new2 $200;
  do over ind;
    if ind then do;
      new=catx(',',new,reason);
      new2=catx(',',new2,put(reason,fff.));
    end;
  end;
  drop reason: ;
run;