DATA Step, Macro, Functions and more

PROC SQL odd value coming out

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 123
Accepted Solution

PROC SQL odd value coming out

[ Edited ]

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.

 

ADD_INFO.PNG


Accepted Solutions
Solution
‎11-01-2016 04:16 PM
Super User
Posts: 5,431

Re: PROC SQL odd value coming out

Posted in reply to JediApprentice
You clearly have a numerical missing value which will be automatically converted to the char <.>.
Data never sleeps

View solution in original post


All Replies
Solution
‎11-01-2016 04:16 PM
Super User
Posts: 5,431

Re: PROC SQL odd value coming out

Posted in reply to JediApprentice
You clearly have a numerical missing value which will be automatically converted to the char <.>.
Data never sleeps
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 234 views
  • 1 like
  • 2 in conversation