topic Re: repeat previous row when missing for each columns in New SAS User

repeat previous row when missing for each columns

Nasser_DRMCP — Thu, 14 Mar 2019 11:18:15 GMT

Hello,

I have a data set with columns an_2005, an_2006.....an_2018.

I succedeed to get

AN_2006 AN_2007 AN_2008 AN_2009 AN_2010 AN_2011 AN_2012 AN_2013 AN_2014 AN_2015

AN_2016 AN_2017 AN_2018 into a macro variable "liste_annee".

Now I would like the non missing value to be repeated whenever a missing value met.

so muy code is :

data t_mois_trsp_A ;
set t_mois_trsp_A ;
array v{*} &liste_annee ;
do i = 1 to dim(v);
	retain value ;
	if not missing(v{i}) then value = v{i} ; else v{i} = value ;
	drop value ;
end;
drop i ; 
run ;

But It does not work as attended, I noticed that values comning from a column (year) are repeated on différents columns.

many thanks in advance for your help

regards

Nasser

Re: repeat previous row when missing for each columns

andreas_lds — Thu, 14 Mar 2019 11:54:06 GMT

No need for macro-variables, you can use an_2008-an_2018 in the array statement. What do you expect if an_2008 is missing?

The statements drop and retain are hardly useful to solve the problem, haven't you read the documentation?

Overwriting a dataset is seldom a good idea, because all steps leading to the data set have to be repeated if something goes wrong.

The following step is untested, if you want tested code, provide data in usable form.

data work.want;
    set t_mois_trsp_A ;
    array v[*] an_2008-an_2018;

    do i = 2 to dim(v);
        if missing(v[i]) then v[i] = v[i-1];
    end;

    drop i;
run;

Re: repeat previous row when missing for each columns

Kurt_Bremser — Thu, 14 Mar 2019 11:54:28 GMT

Maxims 19 & 33.

With a wide format, you need to have a retained variable for every member of your array, and that is done most easily by defining a temporary array:

data have;
input val1 val2 val3;
cards;
1 2 3
2 . 4
3 1 .
. . .
;
run;

%let varlist=val1 val2 val3;
%let arraysize=%sysfunc(countw(&varlist.));

data want;
set have;
array v1 {&arraysize.} &varlist.;
array v2 {&arraysize.} _temporary_;
do i = 1 to dim(v1);
  if not missing(v1{i})
  then v2{i} = v1{i};
  else v1{i} = v2{i};
end;
drop i;
run;

Re: repeat previous row when missing for each columns

andreas_lds — Thu, 14 Mar 2019 11:55:46 GMT

Ignore the code posted, i should have read your post more carefully 😉

Re: repeat previous row when missing for each columns

Kurt_Bremser — Thu, 14 Mar 2019 12:14:23 GMT

The approach for a long dataset looks like this:

data have;
input id $ varnum value;
cards;
a 1 1
a 2 2
a 3 3
b 1 2
b 2 .
b 3 4
c 1 3
c 2 1
c 3 .
d 1 .
d 2 .
d 3 .
;
run;

proc sort data=have;
by varnum id;
run;

data want;
set have;
by varnum;
retain _value;
if first.varnum then _value = .;
if value ne .
then _value = value;
else value = _value;
drop _value;
run;

proc sort data=want;
by id varnum;
run;

This code can easily be expanded to handle more complex groups, whereas in a wide format you'd have to define more and more arrays.

Re: repeat previous row when missing for each columns

Astounding — Thu, 14 Mar 2019 13:09:35 GMT

You have received a number of answers that are close to working. Here's one that combines the best features of all.

First, a question. Do you have AN_2005 in your data, but not in your macro variable? I'm going to assume the answer is yes but if the real answer is no, you might have to adjust slightly by removing AN_2005 from the ARRAY statement..

data want;
   set have;
   array an {*} an_2005 &liste_anee;
   do k=2 to dim(an);
      if an{k} = . then an{k} = an{k-1};
   end;
   drop k;
run;

Re: repeat previous row when missing for each columns

Nasser_DRMCP — Thu, 14 Mar 2019 13:23:31 GMT

thanks all for your precious help.
unfortunatly, I did not succeed with your suggestions.
But I did succeed with this code below

%macro mc_loop_repeat_missing(p_table=t_mois_trsp_A) ;

%let i=1;

%let an_loop = %scan(&liste_annee, &i, %str( ));

%do %while (&an_loop ne );

%let an_loop = %scan(&liste_annee, &i, %str( ));

data &p_table ;

set &p_table ;

retain _&an_loop ;

if not missing(&an_loop) then _&an_loop = &an_loop ; else &an_loop = _&an_loop ;

drop _&an_loop ;

run ;

%LET i = %eval(&i+1);

%let an_loop = %scan(&liste_annee, &i, %str( ));

%end ;

%mend ;