topic Update column data with inner criteria in New SAS User

Update column data with inner criteria

cmemtsa — Wed, 30 Jan 2019 06:32:26 GMT

Hi,

I have this dataset

DATE CF N I

30-Oct-18 A1 1 1

30-Oct-18 A2 1 1

30-Oct-18 A3 2

30-Nov-18 A1 1 1

30-Nov-18 A2 1 1

30-Nov-18 A3

31-Dec-18 A1 1 1

31-Dec-18 A2 1 1

31-Dec-18 A3

31-Dec-18 A4

and because CF A3 has a value of 2 in column I at date 30/10/2018, I want to update column I with 2 for CF=A3 for all subsequent months.

Could you please help me?

Thanks.

Re: Update column data with inner criteria

novinosrin — Wed, 30 Jan 2019 06:48:42 GMT


data have;
input (DATE                                   CF              N                   I_ )(:$10.) ;
cards;
30-Oct-18                            A1                1                   1
30-Oct-18                            A2                1                   1
30-Oct-18                            A3                .                     2
30-Nov-18                           A1                1                   1
30-Nov-18                           A2                1                   1
30-Nov-18                           A3                 .    .               
31-Dec-18                            A1                1                   1
31-Dec-18                            A2                1                   1
31-Dec-18                            A3                 .   .                   
31-Dec-18                            A4                 .  .
; 

data want;
if _n_=1 then do;
declare hash H (dataset: "have(where=(cf='A3' and not missing(i_))") ;
   h.definekey  ("cf") ;
   h.definedata ("i_") ;
   h.definedone () ;
end;
set have;
_iorc_=h.find();
run;

Re: Update column data with inner criteria

novinosrin — Wed, 30 Jan 2019 06:59:26 GMT

proc sql;
create table want as
select DATE,a.CF ,N,coalesce(a.i_,b.i_) as i_       
from have a left join have(keep=cf i_ where=(cf='A3' and not missing(i_))) b
on a.cf=b.cf 
order by date;
quit;

Re: Update column data with inner criteria

cmemtsa — Wed, 30 Jan 2019 07:41:06 GMT

Thanks for the solution but it's not working as it gets an error message. Plus, this is a sample dataset of the actual one where all the CFs with IP=2 at one specific date should be updated with IP=2 for all dates.

Re: Update column data with inner criteria

novinosrin — Wed, 30 Jan 2019 08:00:39 GMT

can you post the log?

Also ,

isn't your original stated requirement was/is to update for all CF='A3'?

Plus can you post a sample of your expected output like your input sample

Re: Update column data with inner criteria

Kurt_Bremser — Wed, 30 Jan 2019 08:14:57 GMT

PLEASE start posting example data in usable form:

data have;
infile cards dsd dlm=' ' truncover;
input date :date9. cf $ n i;
format date yymmddd10.;
cards;
30-Oct-18 A1 1 1
30-Oct-18 A2 1 1
30-Oct-18 A3  2
30-Nov-18 A1 1 1
30-Nov-18 A2 1 1
30-Nov-18 A3
31-Dec-18 A1 1 1
31-Dec-18 A2 1 1
31-Dec-18 A3
31-Dec-18 A4
;
run;

It's not rocket science and makes work much easier for your potential helpers. Consider it a basic courtesy.

From this data, use sorting and a data step with a retained variable:

proc sort data=have;
by cf date;
run;

data want;
set have;
by cf;
retain _i;
if first.cf then _i = .;
i = ifn(_i,_i,i);
if i = 2 then _i = 2;
drop _i;
run;

proc sort data=want;
by date cf;
run;

proc print data=want noobs;
run;

Result:

      date    cf    n    i

2018-10-30    A1    1    1
2018-10-30    A2    1    1
2018-10-30    A3    .    2
2018-11-30    A1    1    1
2018-11-30    A2    1    1
2018-11-30    A3    .    2
2018-12-31    A1    1    1
2018-12-31    A2    1    1
2018-12-31    A3    .    2
2018-12-31    A4    .    .

Re: Update column data with inner criteria

cmemtsa — Wed, 30 Jan 2019 08:16:13 GMT

29 order by date;

ERROR: The variable cf in the DROP, KEEP, or RENAME list has never been referenced.

ERROR: The variable i_ in the DROP, KEEP, or RENAME list has never been referenced.

A more expanded dataset

DATE CF N IP

31-Dec-16 A1 1 1

31-Dec-16 A2 1 1

31-Dec-16 A3 . 2

31-Dec-16 A4 . .

31-Dec-16 A5 . 2

30-Nov-18 A1 1 1

30-Nov-18 A2 1 1

30-Nov-18 A3 . .

30-Nov-18 A4 . .

30-Nov-18 A5 . .

31-Dec-18 A1 1 1

31-Dec-18 A2 1 1

31-Dec-18 A3 . .

31-Dec-18 A4 . .

31-Dec-18 A5 . .

and the desired outcome

DATE CF N IP

31-Dec-16 A1 1 1

31-Dec-16 A2 1 1

31-Dec-16 A3 . 2

31-Dec-16 A4 . .

31-Dec-16 A5 . 2

30-Nov-18 A1 1 1

30-Nov-18 A2 1 1

30-Nov-18 A3 . 2

30-Nov-18 A4 . .

30-Nov-18 A5 . 2

31-Dec-18 A1 1 1

31-Dec-18 A2 1 1

31-Dec-18 A3 . 2

31-Dec-18 A4 . .

31-Dec-18 A5 . 2

Re: Update column data with inner criteria

novinosrin — Wed, 30 Jan 2019 08:27:06 GMT

Thank you @cmemtsa

data have;
input DATE    :date9.                              CF    $            N                  IP ;
format date date9.;
cards;
31-Dec-16                         A1                1                   1
31-Dec-16                         A2                1                   1
31-Dec-16                         A3                .                    2
31-Dec-16                         A4                .                    .
31-Dec-16                         A5                .                    2
30-Nov-18                        A1                1                   1
30-Nov-18                        A2                1                   1
30-Nov-18                        A3                .                    .
30-Nov-18                        A4                .                    .
30-Nov-18                        A5                .                    .
31-Dec-18                         A1                1                   1
31-Dec-18                         A2                1                   1
31-Dec-18                         A3                .                    .
31-Dec-18                         A4                .                    .
31-Dec-18                         A5                .                    .
; 

data want;
if _n_=1 then do;
declare hash H (dataset: "have(where=(ip=2))") ;
   h.definekey  ("cf") ;
   h.definedata ("ip") ;
   h.definedone () ;
end;
set have;
_iorc_=h.find();
run;

Re: Update column data with inner criteria

Kurt_Bremser — Wed, 30 Jan 2019 08:29:57 GMT

Run my code, it gives you the desired result.

@cmemtsa wrote:

29 order by date;

ERROR: The variable cf in the DROP, KEEP, or RENAME list has never been referenced.

ERROR: The variable i_ in the DROP, KEEP, or RENAME list has never been referenced.

A more expanded dataset

DATE                                  CF                N                  IP

31-Dec-16                         A1                1                   1

31-Dec-16                         A2                1                   1

31-Dec-16                         A3                .                    2

31-Dec-16                         A4                .                    .

31-Dec-16                         A5                .                    2

30-Nov-18                        A1                1                   1

30-Nov-18                        A2                1                   1

30-Nov-18                        A3                .                    .

30-Nov-18                        A4                .                    .

30-Nov-18                        A5                .                    .

31-Dec-18                         A1                1                   1

31-Dec-18                         A2                1                   1

31-Dec-18                         A3                .                    .

31-Dec-18                         A4                .                    .

31-Dec-18                         A5                .                    .

and the desired outcome

DATE                                  CF                N                  IP

31-Dec-16                         A1                1                   1

31-Dec-16                         A2                1                   1

31-Dec-16                         A3                .                    2

31-Dec-16                         A4                .                    .

31-Dec-16                         A5                .                    2

30-Nov-18                        A1                1                   1

30-Nov-18                        A2                1                   1

30-Nov-18                        A3                .                    2

30-Nov-18                        A4                .                    .

30-Nov-18                        A5                .                    2

31-Dec-18                         A1                1                   1

31-Dec-18                         A2                1                   1

31-Dec-18                         A3                .                   2

31-Dec-18                         A4                .                    .

31-Dec-18                         A5                .                    2

Re: Update column data with inner criteria

novinosrin — Wed, 30 Jan 2019 08:32:16 GMT

And a similar change in proc sql

proc sql;
create table want as
select DATE,a.CF ,N,coalesce(b.ip,a.ip) as ip       
from have a left join have(keep=cf ip where=(ip=2)) b
on a.cf=b.cf 
order by date,cf;
quit;

Re: Update column data with inner criteria

cmemtsa — Wed, 30 Jan 2019 09:18:45 GMT

Sure I will! Thank you for your help.