topic Re: How do I find and select the first non-missing variable in a group of variables in SAS Programming

How do I find and select the first non-missing variable in a group of variables

Amihic — Thu, 05 Jul 2018 20:02:34 GMT

Hi all,

I have data that looks like this:

subid res1979 res1980 res1981 res1982 res1983 res1984 res1985 res1986 res1987 res1988 res1989

1 . . . 1 1 1 1 2 2 2 2

2 1 1 1 1 1 1 1 1 1 1 1

3 . . . . . . . . 1 1 1

4 . 3 3 3 3 3 3 2 2 2 2

5 . . . . . . . . . . .

n . . . . . 2 2 2 3 3 1

(n = 10,000 subjects)

And I would like it to look like this:

subid firstres

1 1

2 1

3 1

4 3

5 .

n 2

And if possible, like this:

subid firstres yr

1 1 1982

2 1 1979

3 1 1987

4 3 1980

5 . .

n 2 1984

I tried creating an array of the res variables but I do not know how to write the do loop statement to select the first instance of a non-missing column variable.

I am using SAS 9.4.

Any help would be much appreciated.

Regards,

Re: How do I find and select the first non-missing variable in a group of variables

novinosrin — Thu, 05 Jul 2018 19:54:50 GMT

data have;
 input subid  res1979  res1980  res1981  res1982  res1983  res1984  res1985  res1986  res1987  res1988  res1989 ;
cards;
1           .                  .            .             1              1             1              1             2              2            2             2          
2          1                 1           1             1              1             1              1             1              1            1             1     
3           .                  .            .              .               .              .               .              .              1            1             1         
4           .                 3           3             3              3             3              3             2              2            2             2       
5           .                 .            .               .               .              .               .              .              .              .              .         
6          .                 .            .               .               .             2              2             2              3            3              1  
;
data want;
set have;
array t(*) res:;
firstres=coalesce(of t(*));
temp=whichn(firstres,of t(*));
if not missing(temp) then yr=substr(vname(t(temp)),4);
drop res: temp;
run;

Re: How do I find and select the first non-missing variable in a group of variables

Reeza — Thu, 05 Jul 2018 19:56:57 GMT

Transpose the data, delete the missing and then add the numbers? This way you don't need to know which is first.

But does your data match your question??

1 1 1981 -> subject 1 does not have a 1 for 1981.

1. PROC TRANSPOSE

2. Remove missing (could do that in a data step or a data set option with PROC TRANSPOSE).

3. Extract year from the variable name using SCAN() either in same data step.

Untested:

proc transpose data=have out=long(where=(res1 ne .)); prefix=RES; * you may need to confirm column names;

by subid;
var res: ; *colon selects all variables starting with RES;
run;

data want;
set long;

year = input(compress(_name_, , 'ka'), year4.);
format year year4.;

run;

@Amihic wrote:

Hi all,

I have data that looks like this:

subid res1979 res1980 res1981 res1982 res1983 res1984 res1985 res1986 res1987 res1988 res1989

1 . . . 1 1 1 1 2 2 2 2

2 1 1 1 1 1 1 1 1 1 1 1

3 . . . . . . . . 1 1 1

4 . 3 3 3 3 3 3 2 2 2 2

5 . . . . . . . . . . .

n . . . . . 2 2 2 3 3 1

(n = 10,000 subjects)

And I would like it to look like this:

subid firstres

1 1

2 1

3 1

4 3

5 .

n 2

And if possible, like this:

subid firstres yr

1 1 1981

2 1 1979

3 1 1985

4 3 1980

5 . .

n 2 1984

I tried creating an array of the res variables but I do not know how to write the do loop statement to select the first instance of a non-missing column variable.

I am using SAS 9.4.

Any help would be much appreciated.

Regards,

Re: How do I find and select the first non-missing variable in a group of variables

Amihic — Fri, 06 Jul 2018 03:35:33 GMT

Hi PROC Star,

Thank you so much for your help.

At first the code you wrote yielded a warning that said:

WARNING: Defining an array with zero elements

So I just slightly adjusted the code (removing res: and replacing it with CRES1979-CRES2014, which are the names of the vars) and it worked perfectly!

This is my corrected code for others to see:

DATA res;
set temp;
array t(*) CRES1979-CRES2014; *names of the variables I wanted to select the first non-missing from;
firstres=coalesce(of t(*));
other=whichn(firstres,of t(*));
if not missing(other) then yr=substr(vname(t(other)),5);
drop CRES1979-CRES2014 other; *I also dropped the 'other' variable as I didn't need it;
RUN;

Thanks so much!

Re: How do I find and select the first non-missing variable in a group of variables

Amihic — Fri, 06 Jul 2018 03:43:05 GMT

Hi Reeza,

Thank you for the help. Though I didn't use your approach, your code was very helpful for me to understand this particular point:

var res: ; *colon selects all variables starting with RES;

The code provided by PROC Star (above) included a res: ; that I didn't understand at first, but because of your explanation, I can now understand it.

Thanks!

Re: How do I find and select the first non-missing variable in a group of variables

novinosrin — Fri, 06 Jul 2018 03:52:36 GMT

I'm glad. @Amihic all the best and take care. I used stuff to deal with your input data. But you are smart to modify . great!!!!!!!!!

Re: How do I find and select the first non-missing variable in a group of variables

Allaluiah — Fri, 06 Jul 2018 11:11:37 GMT

@Amihic You could credit the help you received by marking their solution as accepted