topic Re: How to retain the values for 'ticked' in SAS Programming

How to retain the values for 'ticked'

Aayushi_17 — Wed, 04 Nov 2020 10:44:09 GMT

Hi all,

In this table how to retain the value of low and high for same subject if the value is ticked.For example please find the table below for refrence.

SUBJECT	BUN_ND_LA	BUN_NHIGH_VA	BUN_NLOW_VA	CALCIUM_ND_LA	CALCIUM_NHIGH_VA	CALCIUM_NLOW_VA	CREAT_CHNG_LA	CREAT_NHIGH_VA	CREAT_NLOW_VA
4001		8.1	2.1		2.65	2.15		2.65	2.15
4001
4001				ticked			ticked
4001				ticked			ticked
4001	ticked			ticked
4001	ticked			ticked
4001	ticked			ticked
4001
1011
1016
3002
3003				ticked
3004
3005								7.2	2.6
5003	ticked			ticked				7.2	2.6
5003	ticked			ticked
5003				ticked
5003	ticked			ticked
5003	ticked			ticked
5003		7.2	2.6				ticked

This how the output should look like :

SUBJECT	BUN_ND_LA	BUN_NHIGH_VA	BUN_NLOW_VA	CALCIUM_ND_LA	CALCIUM_NHIGH_VA	CALCIUM_NLOW_VA	CREAT_CHNG_LA	CREAT_NHIGH_VA	CREAT_NLOW_VA
4001		8.1	2.1		2.65	2.15		2.65	2.15
4001
4001				ticked	2.65	2.15	ticked	2.65	2.15
4001				ticked	2.65	2.15	ticked	2.65	2.15
4001	ticked	8.1	2.1	ticked	2.65	2.15
4001	ticked	8.1	2.1	ticked	2.65	2.15
4001	ticked	8.1	2.1	ticked	2.65	2.15
4001
1011
1016
3002
3003				ticked
3004
3005								7.2	2.6
5003	ticked	7.2	2.6	ticked	2.65	2.15		7.2	2.6
5003	ticked	7.2	2.6	ticked	2.65	2.15
5003				ticked	2.65	2.15
5003	ticked	7.2	2.6	ticked	2.65	2.15
5003	ticked	7.2	2.6	ticked	2.65	2.15
5003		7.2	2.6				ticked	7.2	2.6

Re: How to retain the values for 'ticked'

PeterClemmensen — Wed, 04 Nov 2020 10:34:39 GMT

What is the logic here?

Why does Subject 4001 get the value 8.1 and 5003 the value 7.2?

Re: How to retain the values for 'ticked'

Aayushi_17 — Wed, 04 Nov 2020 10:37:51 GMT

the values vary with different subject...for eg : for subject 4001 the values is 8.1 and 2.1 which will be same for all 4001 subject if it 'ticked'

Re: How to retain the values for 'ticked'

Kurt_Bremser — Wed, 04 Nov 2020 10:38:58 GMT

Please review your expected output. It has 8 observations for 4001, while the source has only 7.

Other subjects (e.g. 1003, 5004) are missing completely.

@Aayushi_17 wrote:

Hi all,

In this table how to retain the value of low and high for same subject if the value is ticked.For example please find the table below for refrence.

SUBJECT BUN_ND_LA BUN_NHIGH_VA BUN_NLOW_VA CALCIUM_ND_LA CALCIUM_NHIGH_VA CALCIUM_NLOW_VA CREAT_CHNG_LA CREAT_NHIGH_VA CREAT_NLOW_VA

4001 8.1 2.1 2.65 2.15 2.65 2.15

4001

4001 ticked ticked

4001 ticked ticked

4001 ticked ticked

4001 ticked ticked

4001 ticked ticked

1003

1011

1016

3002

3003 ticked

3004

3005 7.2 2.6

5003 ticked ticked 7.2 2.6

5003 ticked ticked

5003 ticked

5003 ticked ticked

5003 ticked ticked

5003 7.2 2.6 ticked

5004

This how the output should look like :

SUBJECT BUN_ND_LA BUN_NHIGH_VA BUN_NLOW_VA CALCIUM_ND_LA CALCIUM_NHIGH_VA CALCIUM_NLOW_VA CREAT_CHNG_LA CREAT_NHIGH_VA CREAT_NLOW_VA

4001 8.1 2.1 2.65 2.15 2.65 2.15

4001

4001 ticked 2.65 2.15 ticked 2.65 2.15

4001 ticked 2.65 2.15 ticked 2.65 2.15

4001 ticked 8.1 2.1 ticked 2.65 2.15

4001 ticked 8.1 2.1 ticked 2.65 2.15

4001 ticked 8.1 2.1 ticked 2.65 2.15

4001

1011

1016

3002

3003 ticked

3004

3005 7.2 2.6

5003 ticked 7.2 2.6 ticked 2.65 2.15 7.2 2.6

5003 ticked 7.2 2.6 ticked 2.65 2.15

5003 ticked 2.65 2.15

5003 ticked 7.2 2.6 ticked 2.65 2.15

5003 ticked 7.2 2.6 ticked 2.65 2.15

5003 7.2 2.6 ticked 7.2 2.6

Re: How to retain the values for 'ticked'

Aayushi_17 — Wed, 04 Nov 2020 10:44:45 GMT

sorry the data has been updated now

Re: How to retain the values for 'ticked'

andreas_lds — Wed, 04 Nov 2020 11:21:14 GMT

Is there always exactly one observation having not-missing values for each id? Can you post data in usable form? Usable = a data step with datalines/cards.

Re: How to retain the values for 'ticked'

Aayushi_17 — Wed, 04 Nov 2020 11:34:04 GMT

Hi andreas_ld
No..sometimes the observation might have missing values also..i have jus given them for refernce purpose

Please find the sample data in usable form below
Data have;
input SUBJECT $ BUN_ND_LA $ BUN_NHIGH_VA $ BUN_NLOW_VA $;
datalines;
4001 8.1 2.1
4001
4001
4001
4001 ticked
4001 ticked
4001 ticked
1003
;
run;

Re: How to retain the values for 'ticked'

andreas_lds — Wed, 04 Nov 2020 11:38:25 GMT

My fault, i should have been more precise. I meant: is there always only one observation with values for *_NHIGH_VA, *_NLOW_VA etc. for each subject?

And: please check the code you have posted, i got a "lost card" ...

Re: How to retain the values for 'ticked'

hhinohar — Wed, 04 Nov 2020 15:39:18 GMT

According to what is described here,you want nonmissing values retained for each ticked flag per subject.
If I Look at CALCIUM_NHIGH_VA CALCIUM_NLOW_VA for subject 5003, only missing values exist so these observations should be missing.
Is this a typo or your specification?
Can you please ellaborate on that?

Re: How to retain the values for 'ticked'

mkeintz — Thu, 05 Nov 2020 04:27:06 GMT

For each subject, you apparently have zero or one observation with non-missing BUN_NHIGH_VA and BUN_NLOW_VA. For all observations with BUN_ND_LA='ticked" you want to apply those non-missing values.

This program is untested. If you were to provide your sample data in the form of a working data set, then I could vouch for this code:


data want;
  set have ;
  by subject notsorted;
  if first.subject then ptr=.;
  if BUN_NHIGH_VA^=. and BUN_NLOW_VA^=. then ptr=_n_;

  if last.subject then do until (last.subject);
    set have;
	by subject notsorted;
	if 	BUN_ND_LA='ticked' and ptr^=. then set have (keep=bun_nhigh_va bun_nlow_va) point=ptr;
	output;
  end;
run;

This reads each group twice. The first pass is to identify which observation has the non-missing values, setting ptr to the identified obs numbers. The second pass re-reads the group, and if BUN_ND_LA='ticked' it uses the "point=ptr" option to read the non-missing values prior to the output statement. Note if a group has non non-missing values, then ptr=. and is not used.

Re: How to retain the values for 'ticked'

Kurt_Bremser — Thu, 05 Nov 2020 10:15:45 GMT

When we ask for data in a data step, we mean a data step that works (no ERRORs, WARNINGs or NOTEs) and creates valid data. Please review your code and fix it.

Hint: use an INFILE DATALINES statement with a delimiter (DLM=), DSD and TRUNCOVER.