topic Re: Convert Sybase Procedure to SAS in SAS Procedures

Convert Sybase Procedure to SAS

w020637 — Tue, 19 May 2009 16:20:19 GMT

Hi,

I need some help in converting a Sybase procedure to SAS. I understand Oracle Procedures but new to Sybase.





DECLARE alloc_crsr CURSOR FOR

SELECT distinct 

    i.id,

    i.help_par,

    i.id_pig,

    i.cd_intent,

    i.id_ramesh

FROM $WRK..help_list i

order by i.id

FOR READ ONLY

go



set nocount on

DECLARE 

    @id_pig varchar(10), 

    @pig_seq varchar(10),

    @id varchar(10),

    @id_ramesh varchar(8),

    @cd_intent char(1),

    @amt_par float,

    @split_pct float,

    @help_allocated char(1),

    @prev_id varchar(10)



SELECT @help_allocated = 'N'

SELECT @prev_id = '0'    



OPEN pigs_alloc_crsr

FETCH pigs_alloc_crsr INTO  @id, @amt_par, @id_pig, @cd_intent, @id_ramesh

WHILE (@@sqlstatus = 0)

BEGIN



    IF @prev_id != @id

    BEGIN

        SELECT @help_allocated = 'N'

        SELECT @prev_id = @id

    END



    IF @help_allocated = 'N'

    BEGIN

        SELECT @pig_seq = min(l.pig_seq)

        FROM $WRK..pig_list l

        WHERE l.pig_par = @amt_par

        and l.id_pig = @id_pig

        and l.cd_intent = @cd_intent

        and l.id_ramesh = @id_ramesh



        IF @pig_seq is not null

        BEGIN

            SELECT @help_allocated = 'Y'



            SELECT @split_pct = split_pct

            FROM $WRK..pig_list 

            WHERE id_pig = @id_pig

            AND pig_seq = @pig_seq

            AND pig_par = @amt_par

            AND cd_intent = @cd_intent

            and id_ramesh = @id_ramesh



            INSERT INTO $WRK..final_conv_$TAG

            SELECT

                @id,

                @amt_par,

                @id_pig,

                @id_ramesh,

                @cd_intent,

		@pig_seq,

		@amt_par,

		@split_pct,

		1,

		'1-1 pig_mtch'



	    DELETE $WRK..pig_list

	    WHERE id_pig = @id_pig

	    AND pig_seq = @pig_seq

	    AND cd_intent = @cd_intent

	    AND id_ramesh = @id_ramesh

        END

    END



    COMMIT 



    FETCH pigs_alloc_crsr INTO  @id, @amt_par, @id_pig, @cd_intent, @id_ramesh

END

go



CLOSE pigs_alloc_crsr        

go



DEALLOCATE CURSOR pigs_alloc_crsr

go





Thanks

Re: Convert Sybase Procedure to SAS

sbb — Tue, 19 May 2009 17:08:33 GMT

Just curious if you even attempted to crack the Sybase documentation at all, or are you expecting individuals on the SAS discussion forum to help decode the SYBASE as well as write your SAS program?

It would appear useful to at least attempt on your part to associate some "logic flow" processing comments with the SYBASE program/script while understanding the desired input (source data) and output (SAS data and any reports/graphics), from which a SAS program can be constructed.

Scott Barry
SBBWorks, Inc.

Re: Convert Sybase Procedure to SAS

w020637 — Tue, 19 May 2009 17:27:47 GMT

Thanks Scott,

I am trying to decode this Sybase procedure. I understand what the procedure is doing but there are issues with converting the functionality to SAS.

Here is the process flow:

1. There 3 tables which I can get into a dataset.
2. Basically we loop through each record in table 1 (help_list) and find the minimum pig_seq from table2 (pig_list).
3. Then get the split_pct from that table2 (pig_list) for that particlaur record minimum pig_seq.
4. Store the variables from table1 and table2 in table3.
5. delete the particulr record i.e. minimum pig_seq. from table2 (pig_list).

The challenge in to get the minimum pig_seq from table2.

Regards

Re: Convert Sybase Procedure to SAS

w020637 — Tue, 19 May 2009 17:45:36 GMT

Scott,

As I had previously mentioned that I was working on a solution.

Here is the solution:

1. Sort table 1 on which the join has to be made.
pig_par id_pig cd_intent id_ramesh(key) in ascending

2. Sort table 2 on

pig_par id_pig cd_intent id_ramesh(key) in ascending and pig_seq in descending.

3. Merge the two tables by key.

data combine;
merge table1(in=a) table2(in=b);
by key;
if A;
if first.id_ramesh then output;
run;

Thanks

Re: Convert Sybase Procedure to SAS

w020637 — Tue, 19 May 2009 20:51:25 GMT

Hi,

This is not working and so please ignore the suggested approach. Accept my apologies.

Here is modified process flow still dont know how to convert to sas.

1. There 3 tables which I can get into a dataset.
2. Table 1 has unique records as far as id,help_par,id_pig,cd_intent,id_ramesh is concerned. A particular id should be processed just once.
That is the reason we have two variables
help_allocated = 'N'
prev_id = '0'
to keep track of it.
2. We loop through each record in table 1 (help_list) and find the minimum pig_seq from table2 (pig_list) only if the help_allocated = 'N' for that particlaur id.
If a match is found do step3
3. Then get the split_pct from that table2 (pig_list) for that particlaur record minimum pig_seq.
4. Store the variables from table1 and table2 in table3.
5. delete the particulr record i.e. minimum pig_seq. record from table2 (pig_list).This is a bit tricky as you have to delete the record from a dataset
which is being used in the process(here it is merge).

The challenge is illustrated in the example below:

id,help_par,id_pig,cd_intent,id_ramesh help_par,id_pig,cd_intent,id_ramesh ,pig_seq
1 A B C D A B C D 1
1 X Y Z W A B C D 2
2 A B C D X Y Z W 1
2 X Y Z W A B C D 1
3 A B C D A B C D 1

Suppose this is the dataset. The output(table3) should be

id,help_par,id_pig,cd_intent,id_ramesh help_par,id_pig,cd_intent,id_ramesh ,pig_seq
1 A B C D A B C D 1
1 X Y Z W
2 A B C D A B C D 2
2 X Y Z W
3 A B C D A B C D 1

After deltion the final pig_list should have the following records.

X Y Z W 1
A B C D 1

Regards

Re: Convert Sybase Procedure to SAS

sbb — Tue, 19 May 2009 21:14:37 GMT

From your explanation, possibly a program-flow sequence using multiple sorts (one to filter update candidates; another to de-dup'ing to get one obs with min value from to data-matching table2), merge, with output to a tracking file for identifying deletions in the same DATA step as the MERGE (capture some key variables or _N_ obs # in table 2 for deletion candidate).

Scott Barry
SBBWorks, Inc.