topic Re: Delimiter Issue using Infile in SAS Programming

Delimiter Issue using Infile

gsisme — Thu, 20 Feb 2020 07:36:39 GMT

Hi,

I’m having an issue importing data using the ‘infile’ command. The issue seems too relate to the delimiter, however I cannot find a solution to the issue. I’ve even imported using File->Import and applied these settings however it still will not work. The reason I need to do it manually is that I have 2,500 files to import. Once the logic is fixed, I can nest the code inside a do loop and continue using a data dictionary.

I’ve included the first 5 column headers below; the first 4 will not populate in the output file, the 5th has the data from the first 4 columns bunched together.

data B._20121121;

Informat iMonth BEST12.;

Informat iYear BEST12.;

Informat LEGAL_ENTITY_NUMBER BEST12.;

Informat GL_ACCOUNT BEST12.;

Informat COST_CENTER $20.;

Format iMonth BEST12.;

Format iYear BEST12.;

Format LEGAL_ENTITY_NUMBER BEST12.;

Format GL_ACCOUNT BEST12.;

Format COST_CENTER $20.;

infile 'XXXXX

LRECL=3575

ENCODING="WLATIN1"

TERMSTR=CRLF

DLM='9F'x

MISSOVER

DSD ;

Input iMonth: ?? BEST12.;

Input iYear: ?? BEST12.;

Input LEGAL_ENTITY_NUMBER: ?? BEST12.;

Input GL_ACCOUNT: ?? BEST12.;

Input COST_CENTER $;

Input SECTOR_CODE $;

Input card_assoc_id $;

run;

I've attached a sample of the output.

Does anyone have any ideas on how to proceed? Thanks.

Re: Delimiter Issue using Infile

Kurt_Bremser — Thu, 20 Feb 2020 07:51:02 GMT

Your samples are simple comma-separated, so

infile "....." dlm=',' dsd truncover lrecl=3575;

should be sufficient.

Re: Delimiter Issue using Infile

gsisme — Thu, 20 Feb 2020 08:07:00 GMT

Hi Kurt,

Thanks for the input. I've tried the above solution previously and it seems to duplicate the data from the 1st columns across the remainder of the table, with the exception of the very first observation which remains blank (see attached).

Re: Delimiter Issue using Infile

Kurt_Bremser — Thu, 20 Feb 2020 08:16:18 GMT

I copied your sample2.csv to our UNIX server and ran this:

data want;
infile '$HOME/sascommunity/Sample2.csv' dlm=',' dsd firstobs=2 truncover;
input
  iMonth
  iYear
  LEGAL_ENTITY_NUMBER
  GL_ACCOUNT
  COST_CENTER
;
run;

proc print data=want noobs;
run;

Result:

                  LEGAL_
  i        i     ENTITY_                   COST_
Month    Year     NUMBER    GL_ACCOUNT    CENTER

   .      11        11          11          11  
  11      11        11          11          11  
  11      11        11          11          11  
  11      11        11          11          11  
  11      11        11          11          11  
  11      11        11          11          11  
  11      11        11          11          11  
  11      11        11          11          11  
  11      11        11          11          11  
  11      11        11          11          11  
  11      11        11          11          11  
  11      11        11          11          11  
  11      11        11          11          11

Contents of Sample2.csv, for reference:

iMonth,iYear,LEGAL_ENTITY_NUMBER,GL_ACCOUNT,COST_CENTER
.,11,11,11,11
11,11,11,11,11
11,11,11,11,11
11,11,11,11,11
11,11,11,11,11
11,11,11,11,11
11,11,11,11,11
11,11,11,11,11
11,11,11,11,11
11,11,11,11,11
11,11,11,11,11
11,11,11,11,11
11,11,11,11,11
11,11,11,11,11
11,11,11,11,11
11,11,11,11,11
11,11,11,11,11
11,11,11,11,11
11,11,11,11,11
11,11,11,11,11
11,11,11,11,11
11,11,11,11,11
11,11,11,11,11
11,11,11,11,11
11,11,11,11,11
11,11,11,11,11
11,11,11,11,11
11,11,11,11,11