blank cells

[lalohg]

Hi there,

could you please help?

I have missing entries for ID and PACIENTE in Excel like:

ID	PACIENTE	FECHA	TSH	T4	T3	TIROG
0125168-01	JOHN SMITH	11-nov-13	0.023	11.84	1.34	<0.10
		12-nov-13	63.71	11.61	1.3	0.268

and would like fill blanks with the info (CLAVE AND PACIENTE) above like :

CLAVE	PACIENTE	FECHA	TSH	T4	T3	TIROG
0125168-01	John Smith	11-nov-13	0.023	11.84	1.34	<0.10
0125168-01	John Smith	12-nov-13	63.71	11.61	1.3	0.268

in some cases there are more than one blank row (3 or 4) with different "FECHA"

all your help will be apprecitated

Thanks

[lalohg]

Hi there,

could you please help?

I have missing entries for ID and PACIENTE in Excel like:

ID	PACIENTE	FECHA	TSH	T4	T3	TIROG
0125168-01	JOHN SMITH	11-nov-13	0.023	11.84	1.34	<0.10
		12-nov-13	63.71	11.61	1.3	0.268

and would like fill blanks with the info (CLAVE AND PACIENTE) above like :

CLAVE	PACIENTE	FECHA	TSH	T4	T3	TIROG
0125168-01	John Smith	11-nov-13	0.023	11.84	1.34	<0.10
0125168-01	John Smith	12-nov-13	63.71	11.61	1.3	0.268

in some cases there are more than one blank row (3 or 4) with different "FECHA"

all your help will be apprecitated

Thanks

[PeterClemmensen]

So if you have missing values, you wish to replace these with the last nonmissing value? 🙂

And is CLAVE just a renamed version of ID?

[lalohg]

Hi Draycut,

Yes!  and Yes!

the code tha KurtBremser sent me worked just fine, but thanks anyway. All your help is very much appreciated

lalohg

[Kurt_Bremser · Accepted solution]

Once you have a SAS dataset, do

data want;
set have;
retain _id _paciente;
if missing(id)
then id = _id;
else _id = id;
if missing(paciente)
then paciente = _paciente;
else _paciente = paciente;
drop _id _paciente;
run;

[lalohg]

Hi KurtBremser,

Thank you very much the code worked just fine

have a good day

lalohg

[collinelliot]

I did not try test this - and I think the data you've shown is probably too incomplete to test it accurately - but here's one approach. 

The initial sorting might not be needed, but it will get the non-missing to appear first. Then use a retain to carry the non-missing observations over to the missing. 

proc sort data = have;
    by descending id descending paciente fecha;
run;

data want;
    retain clave paciente2;
    set have;
    if not missing(id) then clave = id;
    if not missing(paciente) then paciente2 = paciente;
run;