topic Re: Find first 5 non missing character values in SAS Programming

Find first 5 non missing character values

nbwest76 — Sun, 28 Jun 2020 05:39:05 GMT

I have a dataset with 22 different character variables named level1-level22. Starting from level22 I would like to search for the first variable that is not missing and then create a new variable using that value called name1 and then do the same for the next non missing variable and create a variable with that value as name2 and so forth. I would like to get the first 5 non missing values so I would like to have the new variables name1-name5 with the first non missing values in order from level22-1.

I believe that using an array is my best bet and I am fairly new at SAS arrays so this is what I have so far.

data want;
	set test;
	array value pos_name_22-pos_name_1;
	array t(*) POS_NAME_22-POS_NAME_1;
	first = coalescec(of pos_name_22-pos_name_1);
	index = whichn(first, of value[*]);

	do _n_=1 to dim(t);
		if missing(t(_n_)) then
			t(_n_)=coalescec(of t(*));
	end;
run;

Re: Find first 5 non missing character values

ChrisNZ — Sun, 28 Jun 2020 06:35:35 GMT

No array or loop needed for just 5 values.

Regardless, this works:

  NAME1=scan(catx(' ',of LEVEL1-LEVEL22),-1);
  NAME2=scan(catx(' ',of LEVEL1-LEVEL22),-2);

Keep things simple!

Re: Find first 5 non missing character values

Patrick — Sun, 28 Jun 2020 07:14:42 GMT

Here how this could work using arrays (code not tested). I've set the length in array name to $20 - set here the same length than what your Level variables got.

data want(drop=_:);
  set test;
  array levels {*} level1 - level22;
  array name {5} $20;
  do _i=dim(levels) to 1 by -1;
    if not missing(levels[_i]) then
      do;
        _k=sum(_k,1);
        name[_k]=levels[_i];
        if _k=5 then leave;
      end;
  end;
run;

Re: Find first 5 non missing character values

ballardw — Sun, 28 Jun 2020 17:23:26 GMT

And what to you expect when there are fewer than 5 non-missing values?