BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Amihic
Fluorite | Level 6

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,

                   

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20
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;

View solution in original post

6 REPLIES 6
novinosrin
Tourmaline | Level 20
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;
Amihic
Fluorite | Level 6

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!

 

novinosrin
Tourmaline | Level 20

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!!!!!!!!!

Reeza
Super User

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,

                   


 

Amihic
Fluorite | Level 6

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!

Allaluiah
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 5347 views
  • 0 likes
  • 4 in conversation