Hi All,
I am having a difficulty in writing a code.
here is the scenario:
patient id | Parity | Date of birth |
731 | . (missing) | 2/5/2010 |
731 | 1 | 7/6/2012 |
For the variable parity, I want to subtract 1 from the adjacent row, to look it like the following:
PPN | Parnum | Date of birth |
731 | 0 | 2/5/2010 |
731 | 1 | 7/6/2012 |
Some of the cases are like this as well:
Patient id | Parity | Date of birth |
5 | 1 | 5/1/2005 |
5 | . | 9/11/2007 |
5 | 3 | 1/1/2009 |
Patient id | Parity | Date of birth |
5 | 1 | 5/1/2005 |
5 | 2 | 9/11/2007 |
5 | 3 | 1/1/2009 |
I appreciate help.
thank you
As parity seems to be counting of observations per patient id,
why is it starting sometimes by 0 and sometimes by 1 ?
It seems to me that next code may do what you want:
data want;
set have;
by patient_id;
retain count; drop count;
if first.patient_id then count=0; else count+1;
if parity = . then do;
if first.patient_id then parity = count;
else parity = count+1;
end;
run;
Does original value of parity is important ? if not it can be simplified into:
data want;
set have;
by patient_id;
retain count; drop count;
if firtst patient_id then count=0; else count+1;
parity = count;
run;
Hi Shmuel,
Many thank for your reply. However, there is still a problem if I am running the code. it is making all my missing as 0. Whereas, If the row after missing has 1, I want 1 - 1=0, whereas, if the row after missing has 2, I want 2-1=1.
The code you mentioned below is doing like this:
If the scenario like this:
Patient id | Parity | Date of birth |
5 | . | 5/1/2005 |
5 | 2 | 9/11/2007 |
5 | 3 | 1/1/2009 |
This is what the code is doing.
Patient id | Parity | Date of birth |
5 | 0 | 5/1/2005 |
5 | 2 | 9/11/2007 |
5 | 3 | 1/1/2009 |
Whereas, I want like this:
Patient id | Parity | Date of birth |
5 | 1 | 5/1/2005 |
5 | 2 | 9/11/2007 |
5 | 3 | 1/1/2009 |
So, whether it 1, 2, or 3 what ever is after missing row in parity, I want to subtract 1 from it.
Many thanks for your help
You can try next code, but what would you like to have if there are more than one missing value in a sequence ?
and what if last patient_id is with parity = . ?
The code is based on sorting parity descending, so that I can get "next" observation parity using LAG function.
proc sort data=have out=temp1; by patient_id parity descending; run;
data temp2;
set temp1;
by patient_id parity descending;
if not first.patiend_id and parity = . then
parity = lag(parity - 1);
run;
proc sort data=have out=temp1; by patient_id parity; run; /* sort back to ascending parity */
There are just only one missing value or would multiple series missing value ? data have; input id Parity Date $; cards; 5 . 5/1/2005 5 2 9/11/2007 5 3 1/1/2009 731 . 2/5/2010 731 1 7/6/2012 5555 1 5/1/2005 5555 . 9/11/2007 5555 3 1/1/2009 ; run; data have; set have; by id; if first.id then n=0; n+1; run; data want; merge have have(keep=id Parity n rename=(Parity=_P n=_n) where=(_P is not missing)); by id; if missing(Parity) then Parity=_p-(_n-n); drop _: n; run;
Here is an approach to replace your existing parity value with a new one. This is a different dataset Have than that provided by
data have;
informat date mmddyy10.;
format data mmddyy10.;
input id Parity Date;
cards;
5
.
5/1/2005
5
2
9/11/2007
5
3
1/1/2009
731
.
2/5/2010
731
1
7/6/2012
5555
2
5/1/2005
5555
.
9/11/2007
5555
3
1/1/2009
;
run;
proc sort data=have;
by id date;
run;
data want;
set have;
by id;
retain newparity;
if first.id then do;
if parity>0 then newparity =Parity;
else Newparity=1;
end;
else newparity=newparity+1;
run;
as I show one mother having an "accurate" non-missing non-zero initial parity.
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.