topic Re: Fill missing values with the previous values in SAS Programming

Fill missing values with the previous values

Demographer — Fri, 20 Jan 2017 12:27:35 GMT

Hello,

I have a dataset that looks like this:

SEX	COUNTRY	PERIOD	X1	X2
MALE	A	2000	2	3
			2	2
			2	2
FEMALE			1	5
			4	1
			2	3
MALE	B		1	3
			4	3
			3	4
FEMALE			5	3
			4	1
			4	1
MALE	C		2	2
…	…	…
	Z	2050	6	4

I want to fill the missing value of SEX COUNTRY and PERIOD with the previous value, until it changes. So it should look like this:

SEX	COUNTRY	PERIOD	X1	X2
MALE	A	2000	2	3
MALE	A	2000	2	2
MALE	A	2000	2	2
FEMALE	A	2000	1	5
FEMALE	A	2000	4	1
FEMALE	A	2000	2	3
MALE	B	2000	1	3
MALE	B	2000	4	3
MALE	B	2000	3	4
FEMALE	B	2000	5	3
FEMALE	B	2000	4	1
FEMALE	B	2000	4	1
MALE	C	2000	2	2
…	…	…
	Z	2050

I'm not really sure how to do this.

Re: Fill missing values with the previous values

Demographer — Fri, 20 Jan 2017 12:35:13 GMT

NVM, I found the solution

data want;
set have;
retain _period;
if not missing(period) then _period=period;
else period=_period;
drop _period;

retain _sex;
if not missing(sex) then _sex=sex;
else sex=_sex;
drop _sex;

retain _country;
if not missing(country) then _country=country;
else country=_country;
drop _country;
run;

Re: Fill missing values with the previous values

Kurt_Bremser — Fri, 20 Jan 2017 12:44:30 GMT

You see that you write a piece of code repeatedly. This is a case for using a macro to reduce your work:

%macro process(variable);
retain help_&variable;
if not missing(&variable)
then _&variable = &variable;
else &variable = _&variable;
drop _&variable;
%mend;

data want;
set have;
%process(sex)
%process(country)
%process(period)
run;

Re: Fill missing values with the previous values

RW9 — Fri, 20 Jan 2017 13:06:02 GMT

A more important question would be why the "data" is like that in the first place. It looks a bit like your reading in a produced report - not a recommended approach for multiple reasons (populations, calculations, assumptions etc.). Get the real "data" and use that.

Re: Fill missing values with the previous values

RW9 — Fri, 20 Jan 2017 13:07:02 GMT

Or use an array and loop over the array rather than bit by bit.

Re: Fill missing values with the previous values

Demographer — Fri, 20 Jan 2017 13:10:27 GMT

I copied/pasted in Excel the output of a proc tabulate and then imported it in SAS. When I tried the out statement, it gaves me only one column for variables X1 X2. I suppose this was probably not the most efficient way.

Re: Fill missing values with the previous values

RW9 — Fri, 20 Jan 2017 13:28:38 GMT

No, its really not. Use out= from tabulate, then manipulate the output dataset to get what you want. Alternative, use one of the other procedures (means/summary/SQL aggregates) or one of the other hundreds of ways to get output you want. For example, your output doesn't make any sense to me:

SEX	COUNTRY	PERIOD	X1	X2
MALE	A	2000	2	3
			2	2
			2	2

Why are there three rows, there is no identifer on each row to say what the rows reflect other then the first one?

Re: Fill missing values with the previous values

Demographer — Fri, 20 Jan 2017 13:30:36 GMT

It is the output of a proc tabulate (cels below were merged).

Re: Fill missing values with the previous values

data_null__ — Fri, 20 Jan 2017 13:31:26 GMT

@Demographer wrote:

I copied/pasted in Excel the output of a proc tabulate and then imported it in SAS. When I tried the out statement, it gaves me only one column for variables X1 X2. I suppose this was probably not the most efficient way.

PROC TABULATE can create a data set directly using OUT= on the PROC statement. This would avoid all the headaches associates with a trip to EXhell and back.

You can more easily fix your data imported from EXhell using the update trick

data haveV/view=haveV;
   set have;
   retain dummy 1;
   run;
data want;
   update haveV(obs=0 keep=dummy) haveV;
   by dummy;
   output;
   drop dummy;
   run;

Re: Fill missing values with the previous values

Demographer — Fri, 20 Jan 2017 13:33:21 GMT

I tried this. The problem is that I want 2 separate column for X1 and X2 (these are 2 categories of a same variable). When I used the out= statement, they appear in a single column.

Re: Fill missing values with the previous values

RW9 — Fri, 20 Jan 2017 13:35:23 GMT

Yes, but it doesn't make much sense.

You have a male, from country A in period 2000, who has reulsts 2,3 (no information as to what the 2/3 represents). You then have two more rows 2/2 2/2. Which is it 2/3, 2/2 or 2/2? Or was it three timepoints within period 2000? Post some test data if you want some code on how to do it directly.

Re: Fill missing values with the previous values

Demographer — Fri, 20 Jan 2017 13:40:38 GMT

Don't pay attention to the data. I cannot copy the real values for confidentiality purposes. These are just random values. There is also another row that I forget to include which was the age group.

Re: Fill missing values with the previous values

RW9 — Fri, 20 Jan 2017 13:48:42 GMT

Ok then, at a guess:

proc means data=have;
  by sex country period age_group;
  var thevar;
  where thevar="A";
  output out=tab1 n=x1;
run;
proc means data=have;
  by sex country period age_group;
  var thevar;
  where thevar="B";
  output out=tab2 n=x2;
run;
data want;
  merge tab1 tab2;
  by sex country period age_group;
run;

You could also proc means to get your overall output then transpose the data up, or count it directly in a datastep etc.

Re: Fill missing values with the previous values

MelissaM — Mon, 02 Oct 2017 18:58:17 GMT

Love this!

Re: Fill missing values with the previous values

Nasser_DRMCP — Thu, 14 Mar 2019 10:19:49 GMT

Hello Kurt,

I succeeded to replace missing values by the previous values.

but only on the column "year_2017" .

my dataset has many columns ... year_2013, year_2014,year_2015,.......year_2018.

how could I apply this code below for each column year_xxxx

retain _an_2017 ;

if not missing(an_2017) then _an_2017 = an_2017 ; else an_2017 = _an_2017 ;

drop _an_2017 ;

thanks a lot in advance

regards

Nasser

Re: Fill missing values with the previous values

Kurt_Bremser — Thu, 14 Mar 2019 11:41:18 GMT

@Nasser_DRMCP wrote:

Hello Kurt,

I succeeded to replace missing values by the previous values.

but only on the column "year_2017" .

my dataset has many columns ... year_2013, year_2014,year_2015,.......year_2018.

how could I apply this code below for each column year_xxxx

retain _an_2017 ;

if not missing(an_2017) then _an_2017 = an_2017 ; else an_2017 = _an_2017 ;

drop _an_2017 ;

thanks a lot in advance

regards

Nasser

Erroneous posting in the wrong topic?

Re: Fill missing values with the previous values

today — Wed, 19 Feb 2020 19:30:30 GMT

I have the newest version of sas 9.4. I used to use this code but now it only works on the first retain statement. All subsequent retain statements give the error message "

retain _analyst;
------
180

ERROR 180-322: Statement is not valid or it is used out of proper order.

any ideas why this doesn't work now?

I tried retain _ALL_ and this doesn't help.

Re: Fill missing values with the previous values

Tom — Wed, 19 Feb 2020 19:42:55 GMT

@today wrote:

I have the newest version of sas 9.4. I used to use this code but now it only works on the first retain statement. All subsequent retain statements give the error message "

retain _analyst;
------
180

ERROR 180-322: Statement is not valid or it is used out of proper order.

"

any ideas why this doesn't work now?

I tried retain _ALL_ and this doesn't help.

Please open a new thread for your question. Include more lines from the log so that the error message is shown in context. Also use the Insert Code button so that formatting (like what word is underlined) is preserved.

Re: Fill missing values with the previous values

pkm_edu — Wed, 22 Feb 2023 16:40:42 GMT

Hello,

What changes would I make to the DATA step below to get the output appended below?

Thanks,

data test;
infile datalines DSD  missover;
input name:$13. School: $30.;
datalines;
,,Pinecrest Elementary School
Craig Smith, 
Paula Johnson, 
Chung Wang,
;
proc print; run;

data test1;
set test;
retain _school;
if not missing(school) then _school=school;
else school = _school;
drop _school;
run;
proc print data=test1;
run;

(Desired output, not generated by the above code blocks)

Obs name School

1 Craig Smith Pinecrest Elementary School
2 Paula Johnson Pinecrest Elementary School
3 Chung Wang Pinecrest Elementary School

Re: Fill missing values with the previous values

Tom — Wed, 22 Feb 2023 17:05:52 GMT

If you want to fix the first step to generate that output without the need for the second step then this is classic example of reading in a report where some "header" information is not repeated. You just need to read the header information once and remember it. Much like the second step is doing with data coming from a dataset instead of a text file.

data test;
  infile datalines dsd truncover ;
  length name $13 _school school $30 ;
  retain school;
  input name _school;
  if not missing(_school) then do;
     school=_school;
     delete;
  end;
  drop _school ;
datalines;
,Pinecrest Elementary School
Craig Smith, 
Paula Johnson, 
Chung Wang,
;

PS I fixed the first line of data so that it has only TWO values and not the THREE it had it what you posted by removing the extra comma.