topic Re: Help, why this won't work? in SAS Programming

Help, why this won't work?

sasuser1000 — Thu, 21 Apr 2011 14:33:39 GMT

Here is my code

data
try1;
input
rec FIRST_CZ NEXT_CZ;
datalines
;
1 . .
2 . .
3 3 5
4 . .
5 . .
6 . .
7 . .
8 . .
;
run
;

data try2;
set try1;
if (lag(FIRST_CZ)>0 and rec first_cz=lag(FIRST_CZ) ;
next_cz=lag(NEXT_CZ);
end;
run;

this is whar I am trying to acheive:

1 . .
2 . .
3 3 5
4 3 5
5 3 5
6 . .
7 . .
8 . .

Re: Help, why this won't work?

buckeye — Thu, 21 Apr 2011 15:24:47 GMT

this should work:

data
try1;
input
rec FIRST_CZ NEXT_CZ;
datalines
;
1 . .
2 . .
3 3 5
4 . .
5 . .
6 . .
7 . .
8 . .
;
run
;

data try2 (DROP = LAG:);
set try1 ;
LAGfirst_cz=lag(FIRST_CZ) ;
LAGnext_cz=lag(NEXT_CZ);
LAG2first_cz=lag2(FIRST_CZ) ;
LAG2next_cz=lag2(NEXT_CZ);

if (LAGFIRST_CZ>0 and FIRST_CZ=.) then do;
first_cz=lagFIRST_CZ ;
next_cz=lagNEXT_CZ;
end; else if (LAGFIRST_CZ=. and LAG2FIRST_CZ>0 and FIRST_CZ=.) then do;
first_cz=lag2FIRST_CZ ;
next_cz=lag2NEXT_CZ;
end;

run;

PROC SQL;
SELECT * FROM TRY2;
QUIT;
run;

Re: Help, why this won't work?

sasuser1000 — Thu, 21 Apr 2011 15:36:16 GMT

Thanks for the reply but I am looking for a more generic code, one that can answer also this

if

1 . .
2 . .
3 3 5
4 . .
5 . .
6 6 10
7 . .
8 . .
9 . .
10 . .
etc...

the results should be

1 . .
2 . .
3 3 5
4 3 5
5 3 5
6 . .
7 7 10
8 7 10
9 7 10
10 7 10
and so forth...

Re: Help, why this won't work?

Doc_Duke — Thu, 21 Apr 2011 15:46:15 GMT

SASuser1000,

The reason that your code didn't work is that the LAG is an executable statement. To be useful, it should rarely be within a DO-END block. You can generalize buckeye's code, though I must say that the output in your second post does not follow a discernible pattern.

Doc Muhlbaier
Duke

Re: Help, why this won't work?

chang_y_chung_hotmail_com — Thu, 21 Apr 2011 15:52:29 GMT

Here is one way. HTH.

   /* test data */

   data one;

     input id start finish;

   cards;

   1 . .

   2 . .

   3 3 5

   4 . .

   5 . .

   6 . .

   7 7 7

   8 8 8

   9 . .

   ;

   run;

   /* carry forward start and finish for id within the range, start-finish, inclusive */

   data two;

      retain qStart qFinish .;

      drop qStart qFinish;

      set one;

      if missing(start) & ^missing(qStart) & qStart<=id<=qFinish then

         link carryForward;

      else

         link queue;

      return;

      carryForward:

         start = qStart;

         finish = qFinish;

      return;

      queue:

         qStart = start;

         qFinish = finish;

      return;

   run;

   proc print data=two;

   run;

   /* on lst

   Obs    id    start    finish

    1      1      .         .

    2      2      .         .

    3      3      3         5

    4      4      3         5

    5      5      3         5

    6      6      .         .

    7      7      7         7

    8      8      8         8

    9      9      .         .

   */

Re: Help, why this won't work?

SPR — Thu, 21 Apr 2011 16:21:20 GMT

Hello SASUser1000,

It looks like you pattern is not very clear. Anyway this is how I understood it:
[pre]
data i;
input rec FIRST_CZ NEXT_CZ;
datalines;
1 . .
2 . .
3 3 5
4 . .
5 . .
6 6 10
7 . .
8 . .
run;
data r;
retain f0 n0;
set i;
f=LAG(FIRST_CZ);
n=LAG(NEXT_CZ);
if f ne . and n ne . then do;
f0=f;
n0=n;
end;
if FIRST_CZ = . then FIRST_CZ=f0;
if NEXT_CZ = . then Next_CZ =n0;
drop n n0 f f0;
run;
[/pre]
Sincerely,
SPR

Re: Help, why this won't work?

sasuser1000 — Thu, 21 Apr 2011 16:27:50 GMT

Chang this code works perfectly. Thank you very much

If you don't mind I have several questions regarding your code:

1) what is link queue;

2)what this do?
carryForward:
first_cz = qStart;
next_cz = qFinish;
return;

Re: Help, why this won't work?

sasuser1000 — Thu, 21 Apr 2011 16:29:49 GMT

Hi SPR,

I really like your code because it's short but it fill rec 6 as well,

record 6 should be

6 . . instead of

6 3 5

i ran it on
data i;
input rec FIRST_CZ NEXT_CZ;
datalines;
1 . .
2 . .
3 3 5
4 . .
5 . .
6 . .
7 7 9
8 . .
9 . .
run;

Re: Help, why this won't work?

sasuser1000 — Thu, 21 Apr 2011 16:55:58 GMT

Hi SPR,

I did a small change to your code added: if(FIRST_CZ = . and rec<=n0) and
if(NEXT_CZ = . and rec<=n0) and it works so well.

Your code is so smart.
thanks