csv-File infile in other format in output file

[Hoibai]

how can I separate a csv infile with this format into a outfile in different columns.

Infile: AD:1/1234 AT:22/234567 .....;

Outfile with columns:

AREA	NUMBER	VALUE
AD	1	1234
AT	22	234567
etc	etc	etc
etc	etc	etc

Thanks.

[yabwon]

Like that:

data want;
  infile CARDS4 DLM = ":/"; /* use multiple delimiters */
  input AREA $ 2. NUMBER VALUE;
cards4;
AD:1/1234 
AT:22/234567
;;;;
run;
proc print;
run;

[Hoibai]

my file look like this:

[yabwon]

Paste it as text, I'm not going to retype it myself...

[mkeintz]

@Hoibai said:

my file look like this:

Please put your data in the form of a text block (use the  </> icon), instead of an image.  Most of us are not going to transcribe the image of characters you provided.

And given that the layout of the additional line of data you provided is different, show how it fits your AREA/NUMBER/VALUE data structure.

[Hoibai]

the read file looks like this:

AD:1/1234,20 AT:2/23456,35 AU:3/34567,59 CA:4/56789,05 CH:5/54321,55;

the output file should be like this:

Columms:

Area    number     value

AD       1                  1234,20

AT       2                  23456,35

AU      3                  34567,59

CA      4                  56789,05

CH      5                  54321,55

[yabwon]

data want;
  infile CARDS4 DLM = ": /"; /* use multiple delimiters */
  input AREA :$2. NUMBER :best32. VALUE :commax32. @@; /* <- stay in the same line for more data */
  format VALUE best32.;
cards4;
AD:1/1234 AT:22/2345,67 XX:17/123,456
ZZ:1/1234 TT:22/2345,67 FU:17/123,456
;;;;
run;
proc print;
run;

[Tom]

Looks good, but you don't need the BEST32.  First of all using BEST as the name of an INFORMAT is just silly.  BEST is the name of a FORMAT.  It is used to find the "best" way to display a number in a limited number of characters.  The concept of a "BEST" way to convert a string into a number makes no sense.  There is only one way to store a number.   If you use BEST as an informat SAS will just use the normal numeric informat anyway so why bother?  And I see no reason to attach the BEST32. format to the values in the example text.  Just let SAS use its default method to display the values.

And there is no need to add a width to a numeric informat when using LIST mode (which is what the colon modifier is doing).  In LIST MODE the width on the informat is ignored, the whole next "word" in the line is read however long it is.  The width on the $ informat does something useful.  It gives the compiler the information it needs to GUESS that you wanted the variable AREA to be define as length $2 instead of the default of $8.

  input AREA :$2. NUMBER VALUE :commax. @@; 

[Hoibai]

How do i can import this CSV File into sas? Thanks.

[Kurt_Bremser]

---

@Hoibai wrote:

How do i can import this CSV File into sas? Thanks.

---

That's exactly what @yabwon's code does. It reads the data into the dataset WANT in library WORK.

To read from a file instead of inline, use the filename in the INFILE statement in place of CARDS, and omit the CARDS4 block.

[Patrick]

---

@Hoibai wrote:

How do i can import this CSV File into sas? Thanks.

---

@yabwon provided already the logic for this. If the first row contains header information then also use in the infile statement firstobs=2

If you want us to share tested code then the minimum you need to provide is representative sample data in usable form. Either posted via the "insert code" icon or as an attachment.

[Reeza]

data want;
  infile '/home/reeza/myfile.csv' DLM = ": /" firstobs=2; /* use multiple delimiters */
*modify to specify the path to your CSV file and change firstobs to indicate the row that the observation is found;
  input AREA :$2. NUMBER :best32. VALUE :commax32. @@; /* <- stay in the same line for more data */
  format VALUE best32.;

;;;;
run;
proc print data=want;
run;

---

@Hoibai wrote:

How do i can import this CSV File into sas? Thanks.

---

[Hoibai · Accepted solution]

I did that, but the file is empty???

data want;
infile '/data/products/Table/Q3_2023/checks_202307.csv'
DLM = ": /" firstobs=2; /* use multiple delimiters */
*modify to specify the path to your CSV file and change firstobs to indicate the row that the observation is found;
input AREA :$2. NUMBER :best32. VALUE :commax32. @@; /* <- stay in the same line for more data */
format VALUE best32.;

;;;;
run;
proc print data=want;
run;

[Tom]

Assuming you did not get an ERROR that the file could not be found then it means that either the file is EMPTY or it has only ONE line.

LOOK at the file and see what it actually has.

Best way is to LOOK at it using a SAS data step, since your goal is to ultimately use SAS to read the file.

This step will print the first 3 lines of your file to the SAS log.

data _null_;
  infile '/data/products/Table/Q3_2023/checks_202307.csv' obs=3;
  input;
  list;
run;

How many lines did it find?

How long were they?

Did the output from the LIST statement include the two lines of hexcodes underneath any parts of the lines?  That will happen when SAS sees an unprintable character code.  Such as LF character ('0A'x) or CR character ('0D'x).

One common problem is that the file does NOT contain lines.  In which case your little program to look at the file will find only ONE line.

If it is using fixed length records, or it is just a binary file, try using the RECFM=N on the INFILE statement.  But you will probably then need to remove the trailing @@ from the INPUT statement to prevent an infinite loop.

Another issue that sometimes comes up is that the file does have lines, but instead of the normal end of line character of LF (used by Unix) or CR and LF (used by Windows, aka DOS) the file is using just CR as the end of line character.  That is what the original MacOS from the 1980's used before they converted MacOS to be a flavor of Unix.  In that case add TERMSTR=CR to the INFILE statement instead.

[Hoibai]

I have another question.

my input file looks like this:

AD:1/1234,20 AT:2/23456,35 AU:3/34567,59 CA:4/56789,05 CH:5/54321,55; DE:3/34567,59 ES:12/2341,25;

If there is a semicolon in the line, then the data up to the next semicolon should be output to another file. Is this visible when reading the CSV file?

Example:

AD:1/1234,20 AT:2/23456,35 AU:3/34567,59 CA:4/56789,05 CH:5/54321,55

Output want;

DE:3/34567,59 ES:12/2341,25

Output want_1