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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

3 REPLIES 3
Reeza
Super User

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;

art297
Opal | Level 21

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;

peppapig
Calcite | Level 5

Thank you so much. This is great!!!

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
  • 3 replies
  • 547 views
  • 0 likes
  • 3 in conversation