how to import a 'character+number' varibale in SAS?

[France]

hello all,

when I import a .csv file in SAS by following code,

the Domain of publn_nr is Up to 15 ASCII characters (since April 2013 without leading spaces) so I add a $ in front of the number, but it still show

data Step2.PubNew2 ;
 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
infile 'R:/L/P/Pub.csv' DLM = ',' DSD missover lrecl = 32767 firstobs = 3 ;
informat pat_publn_id 29. ; 
informat publn_auth $29. ;
informat	publn_nr $29.	;
informat	publn_nr_original 	29.	;
informat	publn_kind	$29. 	;
informat	appln_id 	29.	;
informat	publn_date 	YYMMDD10. 	;
informat	publn_lg 	$29. 	;
informat	publn_first_grant 	29.	;
informat	publn_claims 	29.	;

format 	pat_publn_id 	29.	;
format 	publn_auth 	$29. 	;
format 	publn_nr  $29.	;
format 	publn_nr_original 	29.	;
format 	publn_kind	$29. 	;
format 	appln_id 	29.	;
format 	publn_date 	YYMMDDd10. 	;
format 	publn_lg 	$29.	;
format 	publn_first_grant 	29.	;
format 	publn_claims 	29.	;

input 
pat_publn_id 
publn_auth $ 
publn_nr $
publn_nr_original 
publn_kind $ 
appln_id 
publn_date 
publn_lg $ 
publn_first_grant 
publn_claims ;
  if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
run ;

the Domain of publn_nr is Up to 15 ASCII characters (since April 2013 without leading spaces) so I add a $ in front of the number, but it still shows like this,

NOTE: Invalid data for publn_nr in line 101348 13-19.
RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+---
101348    55765215,US,D584876,,S1,55754619,2009-01-20,en,1,1 50
pat_publn_id=55765215 publn_auth=US publn_nr=. publn_nr_original=. publn_kind=S1 appln_id=55754619
publn_date=2009-01-20 publn_lg=en publn_first_grant=1 publn_claims=1 _ERROR_=1 _N_=101346
NOTE: Invalid data for publn_nr in line 101349 13-19.
101349    55765216,US,D584877,,S1,55754620,2009-01-20,en,1,1 50
pat_publn_id=55765216 publn_auth=US publn_nr=. publn_nr_original=. publn_kind=S1 appln_id=55754620
publn_date=2009-01-20 publn_lg=en publn_first_grant=1 publn_claims=1 _ERROR_=1 _N_=101347

what should I do for it? could you give me some suggestions please?

[SuryaKiran]

In your Log publn_nr=. shows that  publn_nr is numeric missing. I have no issues when I run with those two lines of row data with the code you provided. 

[Tom · Accepted solution]

Does not look like the log messages are for the code you ran since your code is defining PUBLN_NR as character.

It looks like you just copied the code generated by PROC IMPORT?

It might be easier to maintain if you just write your own code instead of adopting the idiosyncrasies of what PROC IMPORT generates.

data Step2.PubNew2 ;
  infile 'R:/L/P/Pub.csv' dlm= ',' dsd truncover firstobs=3 ;
  length
    pat_publn_id 8
    publn_auth $29
    publn_nr $29
    publn_nr_original 8
    publn_kind $29
    appln_id 8
    publn_date 8	
    publn_lg $29
    publn_first_grant 8
    publn_claims 8
  ;
  informat publn_date yymmdd. 	;
  format publn_date yymmddd10. 	;
  input pat_publn_id -- publn_claims ;
run;

[France]

thanks Tom, should I define the variable by the max length if max length of 'pat_publn_id ' is 15? 

[Tom]

---

@France wrote:

thanks Tom, should I define the variable by the max length if max length of 'pat_publn_id ' is 15? 

---

Yes. SAS stores characters as fixed length and numbers as 64-bit (8 bytes) floating point.

If PAT_PUBLN_ID is character then length of $15 should work. 

If PAT_PUBLN_ID is a number then use length of 8. 

Note that is you are using UTF8 characters then you might want to make your variables longer as some UTF8 characters require more than one byte to store.

Note that if your source has a number with more than 15 digits then you better read it as a character variable because 64 bit floating point numbers can only represent 15 digits exactly.

[ballardw]

---

@France wrote:

hello all,

what should I do for it? could you give me some suggestions please?

---

when posting code from the log with data questions like this include the entire data step code that generated the messages along with the message.