Data labeling in SAS, any array???

Accepted Solution Solved
Reply
Contributor
Posts: 42
Accepted Solution

Data labeling in SAS, any array???

DATA HAVE :

ID Q01Q02Q03 Q04FIRST_ANSWERED
1NULLNULLNULLNULL0
1NULL7NULLNULL1
1NULL8960
134560
2NULLNULLNULLNULL0
238921
267800
245020
3NULLNULLNULLNULL0
3452NULL1
323890
323820
438921
465450
463890
4NULLNULLNULLNULL0
438970

WANT TO ADD COLUMN  "FIRST_ANSWERED", this column is labeling the first row by ID that Q1- Q4 are not all "NULL" as 1, otherwise as 0.

Thank you for your help.


Accepted Solutions
Solution
‎11-25-2013 09:20 PM
PROC Star
Posts: 7,471

Re: Data labeling in SAS, any array???

Conversely, if you showed NULL merely to represent missing values, then you might be able to use something like:

DATA HAVE ;

input ID Q01-Q04;

cards;

1 . . . .

1 . 7 . .

1 . 8 9 6

1 3 4 5 6

2 . . . .

2 3 8 9 2

2 6 7 8 0

2 4 5 0 2

3 . . . .

3 4 5 2 .

3 2 3 8 9

3 2 3 8 2

4 3 8 9 2

4 6 5 4 5

4 6 3 8 9

4 . . . .

4 3 8 9 7

;

run;

data want (drop=flag_set);

  set have;

  retain flag_set;

  by id;

  if first.id then flag_set=0;

  first_answered=not flag_set and sum(of q01-q04) gt 0;

  if first_answered then flag_set=1;

run;

View solution in original post


All Replies
Super User
Posts: 19,789

Re: Data labeling in SAS, any array???

Here's one solution, I'm sure there are others...I use a flag to keep track of when the first answered is set and reset at the first of every ID.


DATA HAVE ;

input ID Q01 $ Q02 $ Q03 $ Q04 $ FIRST_ANSWERED;

cards;

1 NULL NULL NULL NULL 0

1 NULL 7 NULL NULL 1

1 NULL 8 9 6 0

1 3 4 5 6 0

2 NULL NULL NULL NULL 0

2 3 8 9 2 1

2 6 7 8 0 0

2 4 5 0 2 0

3 NULL NULL NULL NULL 0

3 4 5 2 NULL 1

3 2 3 8 9 0

3 2 3 8 2 0

4 3 8 9 2 1

4 6 5 4 5 0

4 6 3 8 9 0

4 NULL NULL NULL NULL 0

4 3 8 9 7 0

;

run;

data want;

set have;

retain flag_set;

by id;

if first.id then flag_set=0;

first_answered2=0;

if compress(tranwrd(catt(of Q01-Q04), "NULL", "")) ne "" and flag_set=0 then do;

  first_answered2=1;

  flag_set=1;;

end;

run;

Solution
‎11-25-2013 09:20 PM
PROC Star
Posts: 7,471

Re: Data labeling in SAS, any array???

Conversely, if you showed NULL merely to represent missing values, then you might be able to use something like:

DATA HAVE ;

input ID Q01-Q04;

cards;

1 . . . .

1 . 7 . .

1 . 8 9 6

1 3 4 5 6

2 . . . .

2 3 8 9 2

2 6 7 8 0

2 4 5 0 2

3 . . . .

3 4 5 2 .

3 2 3 8 9

3 2 3 8 2

4 3 8 9 2

4 6 5 4 5

4 6 3 8 9

4 . . . .

4 3 8 9 7

;

run;

data want (drop=flag_set);

  set have;

  retain flag_set;

  by id;

  if first.id then flag_set=0;

  first_answered=not flag_set and sum(of q01-q04) gt 0;

  if first_answered then flag_set=1;

run;

Contributor
Posts: 42

Re: Data labeling in SAS, any array???

Thank you so much. This is great!!!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 221 views
  • 0 likes
  • 3 in conversation