Move data left if values are missing

[kyattayk]

Hello!

I have data that looks like this 

id     HCCategory1 .... HCCategory10     HCCDescrip1.....HCCDescrip10

1               .                           18                          .                    Diabetes

2               57                         .                  Schizophrenia             .

and so on. I want instances like ID 1 to move the categories and their associated descriptions when there is a missing value 

so the data looks like this: 

id     HCCategory1 .... HCCategory10     HCCDescrip1.....HCCDescrip10

1              18                          .                    Diabetes                    .

2              57                          .                  Schizophrenia             .

Each category has 3 associated categories I need to move along with it- the description, the diagnosis code, the diagnosis description. 

Here is what I've written: 

data heirarchy2;
set heirarchy1;
Array HCC2 (60) HCCategory1-HCCategory60;
Array HCCD1 (60) HCCDescrip1-HCCDescrip60;
array DXa (60) dx1- dx60;
array dxd1 (60) DXDescrip1-DXDescrip60;
  do i=1 to 59 ;
  if missing (HCC2[i]) then do j=max(j,i)+1 to dim(HCC2);
    	if not missing (HCC2[j]) then do;
        HCC2[i]=HCC2[j];
        call missing (HCC2[j]);
        leave;
		  	do k=1 to 59 ;
		    	if missing (HCCD1[k]) then do l=max(l,k)+1 to dim(HCCD1);
      				if not missing (HCCD1[l]) then do;
        			HCCD1[k]=HCCD1[l];
        			call missing (HCCD1[l]);
        			leave;
				 		do m=1 to 59 ;
						if missing (DXa[m]) then do n=max(n,m)+1 to dim(DXa);
      							if not missing (DXa[n]) then do;
        						DXa[m]=DXa[n];
        						call missing (DXa[n]);
        						leave;
									do o=1 to 59 ;
									if missing (dxd1[o]) then do p=max(p,o)+1 to dim(dxd1);
      										if not missing (dxd1[p]) then do;
        									dxd1[o]=dxd1[p];
        									call missing (dxd1[p]);
        									leave;
											end;
										end;
									end;
								end;
							end;
						end;
					end;
				end;
			end;
		end;
	end;
end;
  drop i j k l m n o p;
run;

What happens is the first variable,  HCCategories, is properly rolled left, but the 3 associated categories are not. 

Any ideas what I should change? 

[Reeza]

I think it would be easier to move them to a new array/data structure rather than 'move' them.

[Reeza · Accepted solution]

data want;
set have;

Array _HCC2 (60) HCCategory1-HCCategory60;
Array _HCCD1 (60) HCCDescrip1-HCCDescrip60;
array _DXa (60) dx1- dx60;
array _dxd1 (60) DXDescrip1-DXDescrip60;

array HCC2(60) $30. category1-category60;
array hccd1(60) $30.;
array dxa(60) $30.;
array dxd(60) $30.;

index=1;
do i=1 to dim(_HCC2);
if not missing(_hcc2(i)) then do;
hcc2(index)=_hcc2(i);
hccd1(index)=_hccd1(i);
...
...
index+1;
end;
end;

drop [list of older variables];

run;

You need to fill in the blanks but this would be easier to program and check for sure.

---

@kyattayk wrote:

Hello!

I have data that looks like this 

id     HCCategory1 .... HCCategory10     HCCDescrip1.....HCCDescrip10

1               .                           18                          .                    Diabetes

2               57                         .                  Schizophrenia             .

and so on. I want instances like ID 1 to move the categories and their associated descriptions when there is a missing value 

so the data looks like this: 

id     HCCategory1 .... HCCategory10     HCCDescrip1.....HCCDescrip10

1              18                          .                    Diabetes                    .

2              57                          .                  Schizophrenia             .

Each category has 3 associated categories I need to move along with it- the description, the diagnosis code, the diagnosis description. 

Here is what I've written: 

data heirarchy2;
set heirarchy1;
Array HCC2 (60) HCCategory1-HCCategory60;
Array HCCD1 (60) HCCDescrip1-HCCDescrip60;
array DXa (60) dx1- dx60;
array dxd1 (60) DXDescrip1-DXDescrip60;
  do i=1 to 59 ;
  if missing (HCC2[i]) then do j=max(j,i)+1 to dim(HCC2);
    	if not missing (HCC2[j]) then do;
        HCC2[i]=HCC2[j];
        call missing (HCC2[j]);
        leave;
		  	do k=1 to 59 ;
		    	if missing (HCCD1[k]) then do l=max(l,k)+1 to dim(HCCD1);
      				if not missing (HCCD1[l]) then do;
        			HCCD1[k]=HCCD1[l];
        			call missing (HCCD1[l]);
        			leave;
				 		do m=1 to 59 ;
						if missing (DXa[m]) then do n=max(n,m)+1 to dim(DXa);
      							if not missing (DXa[n]) then do;
        						DXa[m]=DXa[n];
        						call missing (DXa[n]);
        						leave;
									do o=1 to 59 ;
									if missing (dxd1[o]) then do p=max(p,o)+1 to dim(dxd1);
      										if not missing (dxd1[p]) then do;
        									dxd1[o]=dxd1[p];
        									call missing (dxd1[p]);
        									leave;
											end;
										end;
									end;
								end;
							end;
						end;
					end;
				end;
			end;
		end;
	end;
end;
  drop i j k l m n o p;
run;

What happens is the first variable,  HCCategories, is properly rolled left, but the 3 associated categories are not. 

Any ideas what I should change? 

---

[ChrisNZ]

I would put the variables in an array and use call sortc().

This will shift all the values as wanted without any further processing.

It is only unsuitable if you want to keep the values in the same order. 

[Reeza]

Does that work when you need to keep things aligned between 4 arrays?

[ChrisNZ]

Oh I see. I definitely read too fast and missed that part of the requirements.

[Reeza]

No worries, I'm notoriously bad for that myself.

[yabwon]

Hi,

maybe try something like this:

data have;
  Array _A_ [*] $ HCCategory1-HCCategory60;
  Array _B_ [*] $ HCCDescrip1-HCCDescrip60;
  array _C_ [*] $ dx1- dx60;
  array _D_ [*] $ DXDescrip1-DXDescrip60;

  do _I_ = 1 to 60 by 3;
    _A_[_I_] = _I_; 
    _B_[_I_] = _I_;
    _C_[_I_] = _I_;
    _D_[_I_] = _I_;
  end;

run;



data want;
 set have; 

  Array _A_ [*] HCCategory1-HCCategory60;
  Array _B_ [*] HCCDescrip1-HCCDescrip60;
  array _C_ [*] dx1- dx60;
  array _D_ [*] DXDescrip1-DXDescrip60;

 _K_ = 1; drop _K_ _I_;
 do _I_ = 1 to dim(_A_);
  if _A_[_I_] NE " " then 
    do; 
      _A_[_K_] = _A_[_I_]; 
      _B_[_K_] = _B_[_I_];
      _C_[_K_] = _C_[_I_];
      _D_[_K_] = _D_[_I_];
      if _K_ < _I_ then 
        do; 
          call missing(_A_[_I_], _B_[_I_], _C_[_I_] , _D_[_I_]);
        end;
      _K_ + 1; 
    end;               
 end;
run;

All the best

Bart