DATA Step, Macro, Functions and more

blank cells

Accepted Solution Solved
Reply
Contributor
Posts: 33
Accepted Solution

blank cells

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

 


Accepted Solutions
Solution
‎02-23-2017 08:24 AM
Super User
Posts: 7,863

Re: blank cells

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Contributor
Posts: 33

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

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

 

PROC Star
Posts: 768

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

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

 

And is CLAVE just a renamed version of ID?

Contributor
Posts: 33

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

Hi Draycut,

 

Yes!  and Yes!

 

the code tha 

 

Solution
‎02-23-2017 08:24 AM
Super User
Posts: 7,863

Re: blank cells

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 33

Re: blank cells

Posted in reply to KurtBremser

 

 

 

PROC Star
Posts: 308

Re: blank cells

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 203 views
  • 1 like
  • 4 in conversation