I posted a "quick reply" but it isn't showing up on my computer, so I'm posting a regular reply...sorry if I am being redundant. I don't think it has to do with ordering the variables in previous steps as SQL does this for you, unlike SAS. They are all the same variable, just different tables. I may try your approach of breaking it up into smaller proc sqls, I like that. Here's the code for review -
proc sql; create table pmsi_tst.phase_1_variables_final as select a.cl_claim_number, b.tramadol_400, c.opioid_benz_sed_stim, d.opioid_benz, e.opioid_carisoprodol, f.opioid_benz_smr, g.stim_sed, h.apap_4k, i.triazolam_LT, j.tempazepam_LT, k.rozerem_LT, l.quazepam_LT, m.flurazepam_LT, n.eszopiclone_LT, o.estazolam_LT, p.zaleplon_LT, q.new_york_brand, r.narc_20_days, s.polypharmacy, t.chronic_cyclo, u.chronic_zolpidem, v.narc_42_days, w.narc_60_days, x.opioid_60_doi, y.narc_30_days, z.smr_6_weeks, aa.benzo_30_days, bb.opioid_3_plus_last_30, cc.chronic_narc, dd.first_narc_trigger, ee.chronic_smr, gg.first_narc_10_ds_trig, ii.new_long_narc, jj.mult_narc_docs, jj.mult_pharm, jj.mult_docs, jj.narc_3_or_more, jj.dupe_smr, jj.dupe_nsaid, jj.dupe_sed, jj.drug_classes_5_plus, rr.cl_55_over_1000_billed, rr.spend_1500_10k, rr.spend_1k_1500, rr.spend_500_1k, a.age_58_60, a.age_60_plus, yy.med_0_50, yy.med_50_100, yy.med_100_150, yy.med_over_150, a.anti_narcoleptic, a.anti_retroviral, a.carisoprodol, a.narcan, a.meperidine, a.opemprazole, a.terocin, a.testosterone, a.phys_dispense, a.anticonvulsant, a.antidepressant, a.anti_inflam, a.antipsych, a.compound, a.non_wc_drug, a.narc_nonnarc_pain, a.opioid, a.steroid, a.ulcer, a.fentanyl, a.duexis, a.vemovo, a.lyrica, a.sed_hyp, a.percocet, a.med5k, xx.celebrex_200, zz.new_LA_tramadol_trig from solo_triggers a left join tramadol_over_400 b on a.cl_claim_number = b.cl_claim_number left join opioid_benz_sed_stim c on b.cl_claim_number = c.cl_claim_number left join opioid_benz d on c.cl_claim_number = d.cl_claim_number left join opioid_carisoprodol e on d.cl_claim_number = e.cl_claim_number left join opioid_benz_smr f on e.cl_claim_number = f.cl_claim_number left join stim_sed g on f.cl_claim_number = g.cl_claim_number left join apap_over_4k h on g.cl_claim_number = h.cl_claim_number left join triazolam_final i on h.cl_claim_number = i.cl_claim_number left join tempazepam_final j on i.cl_claim_number = j.cl_claim_number left join rozerem_final k on j.cl_claim_number = k.cl_claim_number left join quazepam_final l on k.cl_claim_number = l.cl_claim_number left join flurazepam_final m on l.cl_claim_number = m.cl_claim_number left join eszopiclone_final n on m.cl_claim_number = n.cl_claim_number left join estazolam_final o on n.cl_claim_number = o.cl_claim_number left join zaleplon_final p on o.cl_claim_number = p.cl_claim_number left join new_york_brand_final q on p.cl_claim_number = q.cl_claim_number left join narc_20_days r on q.cl_claim_number = r.cl_claim_number left join poly_7 s on r.cl_claim_number = s.cl_claim_number left join chronic_cyclo t on s.cl_claim_number = t.cl_claim_number left join chronic_zolpidem u on t.cl_claim_number = u.cl_claim_number left join narc_42_days v on u.cl_claim_number = v.cl_claim_number left join narc_60_days w on v.cl_claim_number = w.cl_claim_number left join opioid_60_doi x on w.cl_claim_number = x.cl_claim_number left join narc_30_days y on x.cl_claim_number = y.cl_claim_number left join smr_6_weeks z on y.cl_claim_number = z.cl_claim_number left join benzo_30_days aa on z.cl_claim_number = aa.cl_claim_number left join opioids_3_30 bb on aa.cl_claim_number = bb.cl_claim_number left join chronic_narc cc on bb.cl_claim_number = cc.cl_claim_number left join first_second_opioid_trig dd on cc.cl_claim_number = dd.cl_claim_number left join chronic_smr ee on dd.cl_claim_number = ee.cl_claim_number left join first_narc_10_trigger gg on ee.cl_claim_number = gg.cl_claim_number left join new_la_opioid_trig ii on gg.cl_claim_number = ii.cl_claim_number left join multiple_occur_behavior jj on ii.cl_claim_number = jj.cl_claim_number left join spend_variables rr on jj.cl_claim_number = rr.cl_claim_number left join med_all_claims yy on rr.cl_claim_number = yy.cl_claim_number left join celebrex_over_200 xx on yy.cl_claim_number = xx.cl_claim_number left join new_la_tramadol_trig zz on xx.cl_claim_number = zz.cl_claim_number; quit;
... View more
