topic Fill missing data points in table, need help in SAS Procedures

Fill missing data points in table, need help

Ken_oy — Wed, 26 Mar 2014 23:37:21 GMT

I cannot write my own %do %until code to do what I wanted to do.

I have a dataset (please see the first table. I want to convert the first table into the second table. Any missing status in between two "1"s should be marked as "1".

Any help? Thanks!!

-----

ID	Status 1	Status 2	Status 3	Status 4	Status 5	Status 6
1	1	.	.	1	.	.
2	.	1	.	1	.	.
3	1	.	.	.	.	1
4	.	1	.	.	.	.

o, neeStatus 1

ID	Status 1	Status 2	Status 3	Status 4	Status 5	Status 6
1	1	1	1	1	.	.
2	.	1	1	1	.	.
3	1	1	1.	1.	1.	1
4	.	1	.	.	.	.

Status 1

Re: Fill missing data points in table, need help

Reeza — Thu, 27 Mar 2014 00:01:17 GMT

you don't need %do or %until, you need a do loop in a data step.

Pseudocode:

1. Create a string that appends all characters together - use a variation of cat function

2. Find the start, the first 1 - use index function.

3. Find the end, the second1 if it exists, use index function again, reversing the string and searching from the end. subtract from length to find the number.

4. Loop from start to end and replace with 1's

Re: Fill missing data points in table, need help

Ken_oy — Thu, 27 Mar 2014 02:51:22 GMT

Thanks Reeza. Your answer is really helpful!

But in some of my datasets, I have more than 300 status. Creating a new variable might not be long enough to cover all status.

I am still thinking of using a do loop to read the first "1" and the last "1", and then go and follow your instruction 4.

Re: Fill missing data points in table, need help

Reeza — Thu, 27 Mar 2014 03:09:12 GMT

A 400 character length string shouldn't be an issue, just make sure to declare the length ahead of time.

Re: Fill missing data points in table, need help

Praveen_SAS — Thu, 27 Mar 2014 07:06:12 GMT

@ken_oy:

Hi can you have try on this.

data gh;

input a b c h l k p;

datalines;

1 . 1 . 1 . 1

. . 1 . . . 1

. 1 . 1 1 . .

;run;

data fg(drop = x mark i j);

set gh;

array f

_numeric_;

x=0;

do i = 1 to dim(f);

if f ^= . then x+1;

end;

if x > 1 then do;

mark=0;

do j = 1 to dim(f);

if f ^= . then mark+1;

if mark not in (. 0) and x - mark >= 1 and f = . then f = 1;

end;

run;

Re: Fill missing data points in table, need help

data_null__ — Thu, 27 Mar 2014 11:05:59 GMT

Is there ever more than two status variables that are 1?

You show

1 . . 1 . .

but can you have

1 . . 1 . . . 1

and if so what how you fill?

Re: Fill missing data points in table, need help

Astounding — Thu, 27 Mar 2014 14:41:52 GMT

I don't see how macro language would be useful here, but try this:

data want;

set have;

array status {300} status_1 - status_300;

do _n_=1 to 300 until first_1 > .;

if status{_n_}=1 then first_1=_n_;

end;

do _n_=300 to 1 by -1 until last_1 > .;

if status{_n_}=1 then last_1=_n_;

end;

if first_1 < last_1 then do _n_=first_1 to last_1-1;

status{_n_}=1;

end;

drop first_1 last_1;

run;

Re: Fill missing data points in table, need help

data_null__ — Thu, 27 Mar 2014 15:35:55 GMT

Oh well.

data status;
   array status[7];
   array r status7-status1;
   input status;
   f = whichn(1,of status);
   if f then do; 
      l = 1+dim(status)-whichn(1,of r);
      do _n_ = f+1 to l-1; 
         status[_n_]=1; 
         end; 
      end; 
   datalines; 
1 . 1 . 1 . .
. . 1 . . . 1
. 1 . 1 . . .
. . 1 . . . .
. . . . . . .
;;;;
run; 

Re: Fill missing data points in table, need help

asasuser — Thu, 27 Mar 2014 17:43:57 GMT

data status;

array status[6];

array r

status1-status6;

input status

;

do i = 1 to dim(r) while(r(i) = .) ;

End ;

do j = dim(r) to i by - 1 while(r(j) = .) ;

End ;

Do k = i to j ;

r(k) = 1 ;

End ;

drop i j k ;

datalines;

1 . . 1 . .

. 1 . 1 . .

1 . . . . 1

. 1 . . . .

;;;;

run;