topic Re: How do I fill blank cells with the same id and name above? in SAS Programming

blank cells

lalohg — Wed, 22 Feb 2017 20:59:16 GMT

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

How do I fill blank cells with the same id and name above?

lalohg — Wed, 22 Feb 2017 21:02:58 GMT

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

Re: blank cells

Kurt_Bremser — Wed, 22 Feb 2017 21:07:21 GMT

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;

Re: blank cells

collinelliot — Wed, 22 Feb 2017 21:09:06 GMT

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;

Re: How do I fill blank cells with the same id and name above?

PeterClemmensen — Wed, 22 Feb 2017 21:12:48 GMT

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?

Re: blank cells

lalohg — Thu, 23 Feb 2017 13:21:42 GMT

Hi KurtBremser,

Thank you very much the code worked just fine

have a good day

lalohg

Re: How do I fill blank cells with the same id and name above?

lalohg — Thu, 23 Feb 2017 13:23:27 GMT

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