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

Data labeling in SAS, any array???

peppapig — Tue, 26 Nov 2013 01:15:29 GMT

DATA HAVE :

ID	Q01	Q02	Q03	Q04	FIRST_ANSWERED
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

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.

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

Reeza — Tue, 26 Nov 2013 01:38:17 GMT

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;

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

art297 — Tue, 26 Nov 2013 02:20:46 GMT

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;

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

peppapig — Tue, 26 Nov 2013 14:45:13 GMT

Thank you so much. This is great!!!