Problem with array buffer

[deleted_user]

Hello
Here is my problem with array:
I have a dataset and this is how it looks:
ID Code
111 AAA
111 BBB
111 CCC
111 DDD
222 EEE
222 FFF

lets say for a given ID we can have 15 codes(max).
And Iam trying for the below format
ID Code1 Code2 Code3 Code4 Code5 Code6........Code15 I
111 AAA BBB CCC DDD . . . 4
222 EEE FFF . . . . . 2

But here is what Iam getting with the below code.

ID Code1 Code2 Code3 Code4 Code5 Code6........Code15 I
111 AAA BBB CCC DDD . . . 4
222 EEE FFF CCC DDD . . . 2

Here is the code Iam using:

DATA AA;
SET BB;
BY ID;
ARRAY DD {15} $ CODE1-CODE15;
RETAIN CODE1-CODE15;
IF FIRST.ID THEN I=1;
ELSE I+1;
DD{I} = CODE;
IF LAST.ID THEN OUTPUT;
RUN;

with the above details I hope you understood my problem, the buffer is not getting cleared for the second observation, though it has only 2 observations Iam still getting the CCC and DDD from previous ID.(notice the I is showing up correct count)

Is there any function to clear the buffer in SAS or do we have any other way to handle this.

Thanks in advance
Yugandar

[Cynthia_sas]

Hi:
Because you need to retain code1-code15 until you've read all the observations for a single ID, it is up to you to "refresh" or "blank out" all the code values when you know you are done with them. The point in your program where you know you are done with the code values is after you've output the new observation. So one way you could change your program would be to add a DO Loop into your test for LAST.ID:
[pre]
IF LAST.ID THEN do;
OUTPUT;
do i = 1 to 15 by 1;
dd(i) = ' ';
end;
end;
[/pre]

In this way, you would blank out all 15 codes and since you're on the LAST.ID, and have already done the output, then this is one way to reset the values for the DD Array (which holds code1-code15). The other way to blank out the array would be to capture the value of I when you do the output and then use that value (NUMCODE) to stop the DO Loop:
[pre]
IF LAST.ID THEN do;
OUTPUT;
numcode = i;
do i = 1 to numcode by 1;
dd(i) = ' ';
end;
end;
[/pre]

cynthia

[deleted_user]

an interesting aproach can reduce the code, by depending upon some less obvious features of a data step.
The requirement here is simple, and so is the resulting code that follows:[pre]DATA AA( drop= CODE );
ARRAY DD {15} $ CODE1-CODE15;
do i=1 to 15 until( last.ID ) ;
SET BB;
BY ID;
DD{I} = CODE;
end ;
RUN; [/pre]
The SET statement is within the DO loop that terminates at the end of each by-group.
Because the set statement is within the loop, the array elements do not need to be retained. So, the array elements will be reset to missing as each data step iteration starts
On exiting the DO loop the default behaviour of a data step releases the observation without an OUTPUT statement.

The technique of placing a set/merge within an explicit DO loop to take advantage of the initialisation and "temporary retention" of non-retained variables and/or FIRST-, LAST- by-group processing, while reducing the granularity of the data, has been described as a DoW loop. There have been quite a few papers describing this technique on recent SUGI and SAS Global Forums (presumeably also regional and local user groups too). www.lexjansen.com provides an effective search engine.
Definitely worth a read..

PeterC

[deleted_user]

Hello
Both the codes given by Cynthia and Peter has worked, thanks a lot, I have one more.

I have successfully made 1 row per record and now I want to compare a set of codes from the Code1,2,3...15 in the row.

Here is my dataset looks now.

ID Code1 Code2 Code3 Code4 Code5 Code6........Code15 I
111 AAA BBB CCC DDD . . . 4
222 EEE FFF . . . . . 2

and here is the list of codes I want to compare with and assign a flag if there is a match...
Here is the code Iam using:

DO I=1 TO 15 BY 1; (or) DO I=15 TO 1 BY -1 UNTIL(I=1);
IF DD{I} IN ('AAA','BBB','CCC','DDD')
THEN FLAG=1; ELSE FLAG=0;
END;

but its just taking the first or the last observation and comparing.

Can you please help me in this too...Iam a beginner in SAS arrays.

Thanks

[deleted_user]

oops!!! I messed up the thread, the below is my current problem

> Hello
> Both the codes given by Cynthia and Peter has worked,
> thanks a lot, I have one more.
>
> I have successfully made 1 row per record and now I
> want to compare a set of codes from the
> Code1,2,3...15 in the row.
>
> Here is my dataset looks now.
>
> ID Code1 Code2 Code3 Code4 Code5 Code6........Code15
> I
> 1 AAA BBB CCC DDD
> . . . 4
> . . . . 2
> I want to compare with and assign a flag if there is
> a match...
> Here is the code Iam using:
>
> DO I=1 TO 15 BY 1; (or) DO I=15 TO 1 BY -1
> UNTIL(I=1);
> IF DD{I} IN ('AAA','BBB','CCC','DDD')
> THEN FLAG=1; ELSE FLAG=0;
> END;
>
> but its just taking the first or the last observation
> and comparing.
>
> Can you please help me in this too...Iam a beginner
> in SAS arrays.
>
> Thanks

[deleted_user]

I don't understand what you expect (try explaining without using SAS code)
Certainly, by having ELSE, you will only have the result of the last test performed in the loop.
You may have more success if you place the FLAG = 0 ; before the DO loop and remove that else clause. Then FLAG will not be zero if at least one element of the array has a value of the list IN ('AAA','BBB','CCC','DDD')
Depending on the frequency and volume of the data going through this routine, you may want to obtain superior performance available by bypassing the DO loop by using advanced memory management function peekC() as described in a SUGI paper by Paul Dorfman and Peter Crawford 264-29: A-P-P Advanced Data Management Functions http://www2.sas.com/proceedings/sugi29/264-29.pdf .

Good Luck

PeterC

[deleted_user]

> Hello
> Here is my problem with array:
> I have a dataset and this is how it looks:
> ID Code
> 111 AAA
> 111 BBB
> 111 CCC
> 111 DDD
> 222 EEE
> 222 FFF
>
> lets say for a given ID we can have 15 codes(max).
> And Iam trying for the below format
> ID Code1 Code2 Code3 Code4 Code5
> Code6........Code15 I
> 11 AAA BBB CCC DDD . .
> . 4
> . . . .
> . 2
> at Iam getting with the below code.
>
> ID Code1 Code2 Code3 Code4 Code5
> Code6........Code15 I
> 11 AAA BBB CCC DDD . .
> . 4
> CCC DDD . . .
> 2
> the code Iam using:
>
> DATA AA;
> SET BB;
> BY ID;
> ARRAY DD {15} $ CODE1-CODE15;
> RETAIN CODE1-CODE15;
> IF FIRST.ID THEN I=1;
> ELSE I+1;
> DD{I} = CODE;
> IF LAST.ID THEN OUTPUT;
> RUN;
>
> with the above details I hope you understood my
> problem, the buffer is not getting cleared for the
> second observation, though it has only 2 observations
> Iam still getting the CCC and DDD from previous
> ID.(notice the I is showing up correct count)
>
> Is there any function to clear the buffer in SAS or
> do we have any other way to handle this.
>