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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.