topic Re: Update the value in cross format in SAS Programming

Update the value in cross format

Sathish_jammy — Wed, 04 Sep 2019 10:57:16 GMT

Dear Experts,

I'm trying to update the column which already existing in another column using cross level.

In more detail. I share the sample dataset and my required output below.

data aaa;
input ID val1;
cards;
10 0
10 12
10 15
10 14
15 0
15 24
15 26
;

I expect my result as

ID	Val1	Val2
10	0	0
10	12	0
10	15	12
10	14	15
15	0	0
15	24	0
15	26	24

Please let me know if clarification required.

Kindly suggest some ideas to resolve the task.

Thanks in advance!

Re: Update the value in cross format

PeterClemmensen — Wed, 04 Sep 2019 11:00:58 GMT

data aaa;
input ID val1;
cards;
10 0
10 12
10 15
10 14
15 0
15 24
15 26
;

data want;
    set aaa;
    by ID;
    val2=lag1(val1);
    if first.ID then val2=0;
run;

Re: Update the value in cross format

novinosrin — Wed, 04 Sep 2019 11:16:30 GMT

Hi @Sathish_jammy Methinks One IFN will do,


data aaa;
input ID val1;
cards;
10 0
10 12
10 15
10 14
15 0
15 24
15 26
;

data want;
set aaa;
by id;
val2=ifn( first.id=0,lag(val1),0);
run;

Re: Update the value in cross format

Kurt_Bremser — Wed, 04 Sep 2019 11:57:34 GMT

With code, it seems the typical male competition turns the other way:

"Mine is shorter!"

🙂

Re: Update the value in cross format

novinosrin — Wed, 04 Sep 2019 12:03:07 GMT

Hahahahaha, You know what, Let's add to MAXIMS 🙂

Sir @Kurt_Bremser Well, Let me take this opportunity to let you know that since my colleagues at CITIZENS Bank saw the huge printed version of MAXIMS sticking in my desk, everybody in our credit risk/BI started following that. One guy, by the name Connor Dahlman who was a summer intern here has become a big fan of you. 🙂 You are very popular in 1000, Lafayette Blvd