Convert Sybase Procedure to SAS

[w020637]

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

[sbb]

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.

[w020637]

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

[w020637]

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

[w020637]

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

[sbb]

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.