topic Re: Coalesce to collapse variables not working and takes a lot of time in New SAS User

Coalesce to collapse variables not working and takes a lot of time

NewUsrStat — Sat, 01 Jun 2024 10:53:48 GMT

Hi all,

suppose to have the following dataset:

data DB;
  input ID :$20. City :$20. School :$20. Parents :$20.;
cards;
0001 0   0   0
0001 1   1   1
0004 1   0   0
0004 0   0   1
0004 0   0   0
0005 0   1   1
0006 1   0   0
0006 1   1   1
0006 1   1   0
....
;

I'm trying to merge all the variables to get the following:

data DB1;
  input ID :$20. Index :$20.;
cards;
0001 0
0001 1
0004 1
0004 1
0004 0
0005 1
0006 1
0006 1
0006 1
....
;

The rule is: if there is at least one "1" in "City", "School", "Parents" then Index = 1 otherwise 0. I tried to use coalesce but it takes a lot of time because the variables to be collapsed are 10 but the rows are 30.000. Is there an alternative to do the job?

Thank you in advance.

Re: Coalesce to collapse variables not working and takes a lot of time

AhmedAl_Attar — Sat, 01 Jun 2024 11:04:13 GMT

why not use this

data DB(KEEP=ID index);
  input ID :$20. City :$20. School :$20. Parents :$20.;
  
  index = max(city,school,parents);
cards;
0001 0   0   0
0001 1   1   1
0004 1   0   0
0004 0   0   1
0004 0   0   0
0005 0   1   1
0006 1   0   0
0006 1   1   1
0006 1   1   0
....
;
run;

Re: Coalesce to collapse variables not working and takes a lot of time

Patrick — Sat, 01 Jun 2024 11:08:52 GMT

Something like below should work.

data want;
  set have;
  index = whichc('1',city,school,parents) > 0;
run;

Bit weird that variables only containing zero and one are of type character with a length of $20