I took a different approach, using the Lua procedure. The initial step using SQL tells us how wide we need the table to be. We then use that information to generate the blank Got table with the required columns. Finally we parse the Have table into the Got table. In this example, we excluded blank records as a demonstration. I find this type of control structure to be much simpler in Lua than in base SAS. data have;
input offerID degem amnt;
cards;
111123 200 1000
111123 300 2000
111123 400 3000
111124 500 4000
111124 . .
111124 550 4500
11125 600 5000
;
run;
proc sql;
select max(numRecs) into :width
from
(select offerID, count(*) as numRecs
from
have
group by offerID
)
;
quit;
proc lua;
submit;
local have = sas.open("have")
local width = tonumber(sas.symget("width"))
local want_vars = { {name = "offerID", type="N"} }
for idx=1,width do
table.insert(want_vars, {name = string.format("degem_%i",idx), type = "N"})
end
for idx=1,width do
table.insert(want_vars, {name = string.format("Amnt_%i",idx), type = "N"})
end
sas.new_table("got", want_vars)
local got = sas.open("got", "u")
local curr_id
local idx = 0
for row in have:rows() do
if row.offerid ~= curr_id then
if curr_id then
got:update()
end
got:append()
curr_id = row.offerid
got:put_value("offerID", curr_id)
idx = 0
end
if sas.missing(row.degem) == 0 or sas.missing(row.amnt) == 0 then
idx = idx + 1
got:put_value(string.format("degem_%i",idx), row.degem)
got:put_value(string.format("Amnt_%i",idx), row.amnt)
end
end
got:update()
have:close()
got:close()
endsubmit;
run;
... View more
