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 |
What is the logic here?
Why does Subject 4001 get the value 8.1 and 5003 the value 7.2?
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'
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
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.
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;
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" ...
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.