Solved: Re: Update column data with inner criteria

cmemtsa · Posted 01-30-2019 01:32 AM

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.

Kurt_Bremser · Posted 01-30-2019 03:14 AM

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    .    .

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

View solution in original post

novinosrin · Posted 01-30-2019 01:48 AM


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;

novinosrin · Posted 01-30-2019 01:59 AM

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;

cmemtsa · Posted 01-30-2019 02:41 AM

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.

novinosrin · Posted 01-30-2019 02:59 AM

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

cmemtsa · Posted 01-30-2019 03:16 AM

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

novinosrin · Posted 01-30-2019 03:27 AM

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;

novinosrin · Posted 01-30-2019 03:32 AM

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;

Kurt_Bremser · Posted 01-30-2019 03:29 AM

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

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

Kurt_Bremser · Posted 01-30-2019 03:14 AM

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    .    .

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

cmemtsa · Posted 01-30-2019 04:18 AM

Sure I will! Thank you for your help.

SAS Innovate 2025: Call for Content