topic Re: Delimiter not working during import of data in SAS Programming

Delimiter not working during import of data

Q1983 — Sat, 22 May 2021 01:57:10 GMT

options validvarname=v7;
data My_Report;
infile '/myshare/report1.csv' 
delimiter = ','  firstobs=2 ;
input Item_ID $9.     /*$ 1-9 $9. */			
			Item_Type $7.    /*$11-16    $7.*/
			Description $
                        Email $
;run;

The above code produces this

Item_ID	Item_Type	Description	Email
MRG570-15	,CENTRAL	XYZ Cert	manning@email.com
MRG570-15	,CENTRAL	XYZ Cert	jrobinsonsmith@myemail.com
MRG570-15	,CENTRAL	XYZ Cert	walterjones@remmington.Com

1. Notice in Item_Type it begins with the comma I want the data to read to the comma delimiter then read the next variable. In this case it began with the comma.

2. In the Description it only reads the first 8 places. XYZ Cert. It is not reading to the comma despite me placing the delimiter requirement in the code. Is there a way to read in the data more clean?? As you can see I tried defining the length however I have other fields to import such as email which will have varying lengths.

Re: Delimiter not working during import of data

Kurt_Bremser — Sat, 22 May 2021 06:53:32 GMT

Please post an example of your csv file, or attach it to the post.

To post an example, open the file with a text editor (not with Excel!) and copy/paste a few lines into a code box opened with the </> button.

Re: Delimiter not working during import of data

Kurt_Bremser — Sat, 22 May 2021 06:57:01 GMT

By using formatted input for the first two variables, you override the delimiter. Use the colon modifier to prevent this:

input
  Item_ID :$9.	
  Item_Type :$7.

Re: Delimiter not working during import of data

Tom — Sat, 22 May 2021 14:36:18 GMT

I find it is worth the extra line of code to actual DEFINE the variables instead of forcing SAS to guess how to define them based on how they are first used.

Use a LENGTH statement to define the variables. Use a length of 8 for numeric variables (SAS stores numbers as 64-bit floating point values). Set the character variables long enough to hold the longest value you expect. Currently you are letting DESCRIPTION and EMAIL default to length just 8 bytes. Which is probably too short for variables with names like that.

If you define the variables in the order they appear in the file your INPUT statement can just a position based variable list.

data My_Report;
  infile '/myshare/report1.csv' dsd firstobs=2 truncover ; 
  length Item_ID $9 Item_Type $7 Description $40 Email $50 ;
  input Item_ID -- Email;
run;

The TRUNCOVER option will prevent the INPUT statement from jumping to the next line if there are not enough values on the current line.