topic Re: Move data left if values are missing in SAS Programming

Move data left if values are missing

kyattayk — Thu, 20 Feb 2020 19:48:14 GMT

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?

Re: Move data left if values are missing

Reeza — Thu, 20 Feb 2020 19:56:12 GMT

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

Re: Move data left if values are missing

Reeza — Thu, 20 Feb 2020 19:59:36 GMT

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?

Re: Move data left if values are missing

ChrisNZ — Thu, 20 Feb 2020 20:00:14 GMT

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.

Re: Move data left if values are missing

Reeza — Thu, 20 Feb 2020 20:05:40 GMT

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

Re: Move data left if values are missing

yabwon — Thu, 20 Feb 2020 20:12:41 GMT

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

Re: Move data left if values are missing

ChrisNZ — Thu, 20 Feb 2020 21:44:11 GMT

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

Re: Move data left if values are missing

Reeza — Thu, 20 Feb 2020 21:14:51 GMT

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