Solving the LinkedIn Queens Puzzle with PROC OPTMODEL
Recent Library Articles
Recently in the SAS Community Library: SAS' @SubbuPaz and @RobPratt formulate the LinkedIn Queens Puzzle as an integer programming model and solve it using PROC OPTMODEL in SAS Optimization.
Hello everyone,
I'm working with SAS VA on SAS Viya 4, and I need to create a report that queries data directly from Hive without loading the entire table into CAS.
The table is very large grows daily and is partitioned by year, month, and day. I want users to be able to select parameters (year, month, day) in the report and the query should retrieve only the relevant partitions instead of scanning the whole table.
If anyone has worked on a similar case, could you please share the steps to achieve this? Thanks.
... View more
Hello,
I think there is another tiny mistake in the answer for practice s102s03, on page 107 in SQL1: essentials course note PDF (screenshot is as below). The merchant name of December's first transaction is Sceneit Cinemas, LLC, not Big Burgers, Inc. I run both my code and code provided by the course note and had the same results (see below).
title "November/December Transactions";
proc sql;
select customername label='Customer Name',
merchantname label='MerchantName',
amount label='Transaction Amount'
format=dollar10.2,
datepart(datetime) as transactiondate
label='Transaction Date'
format=date9.
from sq.transactionfull
where month(calculated transactiondate)in(11,12) and
service ^= 'University'
order by datetime;
quit;
title;
title "November/December Transactions";
proc sql;
select customername label='Customer Name',
merchantname label='MerchantName',
amount label='Transaction Amount'
format=dollar10.2,
datepart(datetime) as transactiondate
label='Transaction Date'
format=date9.
from sq.transactionfull
where month(datepart(datetime)) in (11,12) and
service ^= 'University'
order by datetime;
quit;
title;
Also, I can see from the first five rows that the above result table (see below, the lower one) is the same as the partial result table provided with the original question on course note PDF, page 65 (see below, the upper one).
... View more
I attached my code below. I am running into a problem where in my first left join table beneish_m is not continuing to sort the gvkeys in order. Then, when creating the second left join table merge_back, it is not pulling the deleted duplicate beneish indexs but instead pulling all the beneish indexs instead. But before running the 2nd left join the code is properly deleting the duplicated keys. Any advice will help. Data Work.combined; Set fsa.compustat_short; Set fsa.aaer_combined; Run; proc sort data=Work.combined; by gvkey datadate; run; data tot_accr; set combined; d_CA=ACT-lag(ACT); d_CL=LCT-lag(LCT); d_Cash=CH-lag(CH); d_STD=DLC-lag(DLC); lag_AT=lag(AT); TA=(d_CA - d_CL - d_Cash + d_STD - DP)/lag_AT; by gvkey DATADATE; if first.gvkey then delete; KEEP lag_Rect lag_Revt lag_COGS lag_ACT lag_AT lag_DP lag_PPEGT lag_LCT lag_XSGA lag_DLTT gvkey fyear RECT REVT COGS ACT AT PPEGT LCT XSGA DLTT CIK SIC lag_CH CH lag_TXP TXP DLCCH TA SIC2 lag_dlc dlc d_CA d_CL d_Cash d_STD DP run; data mjones; set tot_accr; sic2=put(substr(sic,1,2),2.); if AT ge 0; if ACT=. then delete; run; proc sort data=mjones; by gvkey datadate; run; data lagged_variables; set mjones; lag_Rect=lag(RECT); lag_Revt=lag(REVT); lag_COGS=lag(COGS); lag_ACT=lag(ACT); lag_DP=lag(DP); lag_PPEGT=lag(PPEGT); lag_LCT=lag(LCT); lag_XSGA=lag(XSGA); lag_DLTT=lag(DLTT); lag_CH=lag(CH); lag_TXP=lag(TXP); lag_dlc=lag(dlc); by gvkey DATADATE; if first.gvkey then delete; KEEP lag_Rect lag_Revt lag_COGS lag_ACT lag_AT lag_DP lag_PPEGT lag_LCT lag_XSGA lag_DLTT gvkey fyear RECT REVT COGS ACT AT PPEGT LCT XSGA DLTT CIK SIC lag_CH CH lag_TXP TXP DLCCH TA SIC2 lag_dlc dlc d_CA d_CL d_Cash d_STD DP; run; data blank_variables; set lagged_variables; if lag_Rect=. then delete; if lag_Revt=. then delete; if lag_COGS=. then delete; if lag_ACT=. then delete; if lag_DP=. then delete; if lag_PPEGT=. then delete; if lag_LCT=. then delete; if lag_XSGA=. then delete; if lag_DLTT=. then delete; if lag_CH=. then delete; if lag_TXP=. then delete; if lag_AT=. then delete; if lag_DLC=. then delete; run; data Beneish_variables; set blank_variables; DRSI=(RECT/REVT)/(lag_Rect/lag_Revt); GMI=((lag_Revt-lag_COGS)/lag_Revt)/((REVT-COGS)/REVT); AQI=((1-(ACT+PPEGT))/AT)/((1-(lag_ACT+lag_PPEGT))/lag_AT); SGI=REVT/lag_Revt; DEPI=(lag_DP/(lag_DP+Lag_PPEGT))/(DP/(DP+PPEGT)); LVGI=((DLTT+LCT)/AT)/((lag_DLTT+lag_LCT)/lag_AT); SGAI=(XSGA/REVT)/(lag_XSGA/lag_Revt); run; data Creating_key; set Beneish_variables; key =cats(fyear,SIC2); if CIK =. then delete; Run; proc sql; create table beneish_m as select creating_key.*, aaer_combined.* from work.creating_key as a LEFT JOIN fsa.aaer_combined as b on a.cik=b.cik and a.fyear=b.year; run; quit; proc sort data=beneish_m; by gvkey datadate; run; data beneish_index; set beneish_m; key_ind=1; keep key key_ind; run; proc sort data=beneish_index nodup; by key; run; proc sql; create table merge_back as select beneish_index.*,beneish_m.* from work.beneish_index as a LEFT JOIN work.beneish_m as b on a.key=b.key; run; quit;
... View more
Hello. I am conducting mi procedure. My outcome is 3 variables. I used fcs logistic. step 1/* Impute missing data using logistic regression */ proc mi data=mydata_mental seed=12345 nimpute=20 out=outfcs_impute; where _age_g=6; class ment14d_w fall12mn_w _sex age65plus_w urbstat_w educag_w racegr4_w income_w marital_w hlthpl1_w persdoc3_w medcost1_w checkup1_w exerany2_w; fcs logistic (ment14d_w/details) logistic (income_w) ; var ment14d_w fall12mn_w _sex age65plus_w urbstat_w educag_w racegr4_w marital_w hlthpl1_w persdoc3_w medcost1_w checkup1_w exerany2_w _ststr _psu _llcpwt income_w; run; step 2 /* Analyze each imputed dataset using logistic regression */ This works ******** proc logistic data = outfcs_impute; Class ment14d_w (ref="0") fall12mn_w (ref="0") _sex (ref="1") age65plus_w (ref="1") urbstat_w(ref="1") educag_w (ref="1") racegr4_w(ref="1") income_w(ref="1") marital_w(ref="1") hlthpl1_w(ref="0") persdoc3_w(ref="0") medcost1_w(ref="0") checkup1_w(ref="0") exerany2_w(ref="0")/param=ref; model ment14d_w = fall12mn_w _sex age65plus_w urbstat_w educag_w racegr4_w income_w marital_w hlthpl1_w persdoc3_w medcost1_w checkup1_w exerany2_w / link = glogit covb; by _imputation_; ods output ParameterEstimates=lgsparms CovB=lgscovb; run; /* Pool results across imputed datasets */ (this works but I do not get coefficient for (3 categories for dependent var) proc mianalyze parms(classvar=classval)=lgsparms; class ment14d_w fall12mn_w _sex age65plus_w urbstat_w educag_w racegr4_w income_w marital_w hlthpl1_w persdoc3_w medcost1_w checkup1_w exerany2_w; modeleffects intercept ment14d_w fall12mn_w _sex age65plus_w urbstat_w educag_w racegr4_w income_w marital_w hlthpl1_w persdoc3_w medcost1_w checkup1_w exerany2_w; run; My questions. How can I get odds ratio (in simple step) in pooled results after mianalyze? How can I get coefficient value for final output after mianalyze?
... View more
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Nominations are in, and the SAS Customer Recognition Awards voting is complete! Winners get a full trip to SAS Innovate (May 6-9) in Orlando, FL! See the 60+ inspiring entries from SAS users! Winners will be announced at SAS Innovate!