BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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

1 ACCEPTED SOLUTION

Accepted Solutions
LinusH
Tourmaline | Level 20
You clearly have a numerical missing value which will be automatically converted to the char <.>.
Data never sleeps

View solution in original post

1 REPLY 1
LinusH
Tourmaline | Level 20
You clearly have a numerical missing value which will be automatically converted to the char <.>.
Data never sleeps

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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