I'm getting a weird value for variable ADD_INFO when the query finishes - at first it looks like a missing numeric value but it is not right justified - its more towards the middle-left, and it is this value for all records. I've inserted a picture of the values at the bottom. Here is the code that generated them:
PROC SQL;
CREATE TABLE Claims_Detail_Add_Funding AS
SELECT a.*,
(CASE
WHEN j.'GROUP NUMBER'n NOT IS NULL THEN j.'GROUP NUMBER'n
WHEN put(k.'Group Number'n,z6.) NOT IS NULL THEN put(k.'Group Number'n,z6.)
WHEN l.Group6_6 NOT IS NULL THEN l.Group6_6
WHEN d.CarrierID NOT IS NULL THEN 'MN Service Coops'
WHEN e.AccountID NOT IS NULL THEN e.FundTypeDescriptor
WHEN f.AccountID NOT IS NULL THEN f.FundTypeDescriptor
WHEN g.AccountID NOT IS NULL THEN g.FundTypeDescriptor
WHEN i.AccountID NOT IS NULL THEN i.FundTypeDescriptor
WHEN m.AccountID NOT IS NULL THEN m.FundTypeDescriptor
WHEN h.Category = 'Individual' THEN h.Category
WHEN h.Category ne 'Individual' THEN h.'Underwritten/Self Funded'n
ELSE ""
END) AS ADD_INFO
FROM All_Claims_Detail a
LEFT JOIN (SELECT DISTINCT CarrierID, AccountID, GroupID, PlanCode FROM pmn1.group WHERE substr(GRPContactName,5,3) IN ('BC3','AB4')) d
ON (a.CarrierID = d.CarrierID AND a.AccountID = d.AccountID AND a.GroupID = d.GroupID)
LEFT JOIN pilscrp1.HCSC_ACCOUNT e ON (a.AccountID = e.AccountID AND a.GroupID = e.GroupID AND a.CarrierID = 'IL8060')
LEFT JOIN pildrug1.HCSC_ACCOUNT f ON (a.AccountID = f.AccountID AND a.GroupID = f.GroupID AND a.Carrier_Client = 'IL')
LEFT JOIN pnm1.HCSC_ACCOUNT g ON (a.AccountID = g.AccountID AND a.GroupID = g.GroupID AND a.Carrier_Client = 'NM')
LEFT JOIN ptx1.HCSC_ACCOUNT i ON (a.AccountID = i.AccountID AND a.GroupID = i.GroupID AND a.Carrier_Client = 'TX')
LEFT JOIN pmt1.HCSC_ACCOUNT m ON (a.AccountID = m.AccountID AND a.GroupID = m.GroupID AND a.Carrier_Client = 'HM')
LEFT JOIN AL_SPLITS h ON (a.CarrierID = h.CarrierID AND a.AccountID = h.AccountID AND a.GroupID = h.GroupID)
LEFT JOIN FL_ASO j ON (a.Group3_5 = j.'GROUP NUMBER'n AND a.Carrier_Client = 'FL')
LEFT JOIN ND_ASO k ON (a.Group6_6 = put(k.'Group Number'n,6.) AND a.Carrier_Client = 'ND')
LEFT JOIN NE_ASO l ON (a.Group6_6 = l.Group6_6 AND a.Carrier_Client = 'NE');
QUIT;
And here are some messages from the log - not sure if they're relevant:
NOTE: User pta63848(ACL Group ) connected to SPD(AIX) 4.50(TS M2) HF(9) server at 10.110.4.111.
NOTE: Read-only access to LIBNAME domain restricted by *LIBNAM* ACL.
NOTE: Physical Name: PMN1
SPDS_NOTE: Read-only access to LIBNAME domain restricted by *LIBNAM* ACL.
SPDS_NOTE: Libref X0000005 was successfully assigned as follows:
Engine: SPDSENG
Physical Name: :14025058/spdsmeta/bcbsmn/current/
SPDS_NOTE: Read-only access to LIBNAME domain restricted by *LIBNAM* ACL.
SPDS_NOTE: Libref PMN1 was successfully assigned as follows:
Engine: SPDSENG
Physical Name: :14025058/spdsmeta/bcbsmn/current/
NOTE: SAS threaded sort was used.
NOTE: Parallel WHERE evaluation suppressed due to sort order on table(see SPDSEV1T/SPDSEV2T macro variables).
NOTE: Compressing data set WORK.CLAIMS_DETAIL_ADD_FUNDING decreased size by 44.71 percent.
Compressed is 4909 pages; un-compressed would require 8879 pages.
NOTE: Table WORK.CLAIMS_DETAIL_ADD_FUNDING created, with 719170 rows and 80 columns.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.