topic Re: csv-File infile in other format in output file in SAS Programming

csv-File infile in other format in output file

Hoibai — Wed, 07 Feb 2024 12:24:54 GMT

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.

Re: csv-File infile in other format in output file

yabwon — Wed, 07 Feb 2024 12:49:50 GMT

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;

Re: csv-File infile in other format in output file

Hoibai — Wed, 07 Feb 2024 13:00:29 GMT

my file look like this:

Re: csv-File infile in other format in output file

yabwon — Wed, 07 Feb 2024 13:22:31 GMT

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

Re: csv-File infile in other format in output file

mkeintz — Wed, 07 Feb 2024 13:34:40 GMT

@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.

Re: csv-File infile in other format in output file

yabwon — Wed, 07 Feb 2024 13:28:38 GMT

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;

Re: csv-File infile in other format in output file

Hoibai — Wed, 07 Feb 2024 13:45:42 GMT

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

Re: csv-File infile in other format in output file

Tom — Wed, 07 Feb 2024 14:08:37 GMT

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. @@;

Re: csv-File infile in other format in output file

Hoibai — Wed, 07 Feb 2024 18:38:50 GMT

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

Re: csv-File infile in other format in output file

Kurt_Bremser — Wed, 07 Feb 2024 18:54:48 GMT

@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.

Re: csv-File infile in other format in output file

Patrick — Wed, 07 Feb 2024 22:29:52 GMT

@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.

Re: csv-File infile in other format in output file

Reeza — Wed, 07 Feb 2024 23:19:43 GMT

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.

Re: csv-File infile in other format in output file

Hoibai — Thu, 08 Feb 2024 05:36:26 GMT

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;

Re: csv-File infile in other format in output file

Hoibai — Thu, 08 Feb 2024 05:47:38 GMT

sorry my mistake. It worked :-). Thank you very much.

Re: csv-File infile in other format in output file

Tom — Thu, 08 Feb 2024 05:48:33 GMT

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.

Re: csv-File infile in other format in output file

Hoibai — Mon, 12 Feb 2024 15:18:14 GMT

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

Re: csv-File infile in other format in output file

Tom — Mon, 12 Feb 2024 15:55:24 GMT

Please learn how to use the Insert Code and Insert SAS Code pop-up windows.

Is your data all on one line?

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;

Or on separate lines?

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;

Could one "group" span multiple lines?

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;

How long is the longest line? If you just read the whole file SAS will tell you. Here is a simple example:

1    options parmcards=csv;
2    filename csv temp;
3    parmcards4;
7    ;;;;

8
9    data _null_;
10     infile csv ;
11     input;
12   run;

NOTE: The infile CSV is:
      Filename=C:\Users\ABERNA~1\AppData\Local\Temp\1\SAS Temporary Files\_TD23280_AMRAPY3WVP0VKU0_\#LN00009,
      RECFM=V,LRECL=32767,File Size (bytes)=102,
      Last Modified=12Feb2024:10:39:49,
      Create Time=12Feb2024:10:39:49

NOTE: 3 records were read from the infile CSV.
      The minimum record length was 28.
      The maximum record length was 40.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      cpu time            0.00 seconds

As you can see there where 3 lines of data and the longest line was 40 bytes and the shortest was 28 bytes.

If the lines are short (less then 32k bytes per line) then you can check for the semicolon.

Your files appears to consist of space delimited values that each consist of three internal values. Let's call these a TRIPLET. So here is a program to create the data with an additional GROUP variable that counts how man semicolon group separators have been seen.

data test;
  infile csv dlm=' ' ;
  retain group 1;
  length triplet $40 area $2 number value 8;
  input triplet @@;
  area = scan(triplet,1,':');
  number = input(scan(triplet,2,':/'),commax32.);
  value = input(scan(triplet,3,':/;'),commax32.);
  output;
  if indexc(triplet,';') then group+1;
  drop triplet;
run;

Result

Obs    group    area    number      value

 1       1       AD        1       1234.20
 2       1       AT        2      23456.35
 3       1       AU        3      34567.59
 4       1       CA        4      56789.05
 5       1       CH        5      54321.55
 6       2       DE        3      34567.59
 7       2       ES       12       2341.25

Re: csv-File infile in other format in output file

Hoibai — Mon, 12 Feb 2024 16:01:14 GMT

yes, my data is all on one line.

Re: csv-File infile in other format in output file

Hoibai — Mon, 12 Feb 2024 16:34:02 GMT

my data line is linesize=32767 bytes long. Your Codes skip a few semicolons.

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;AD:1/205,92;DE:25/2039,05;

Re: csv-File infile in other format in output file

Tom — Mon, 12 Feb 2024 16:46:04 GMT

32,767 is the default LRECL. So your actual file might have lines longer than that. You can use LRECL= values up to 10 million or so depending on your machine.

But if the file is not intended to have lines then just use RECFM=N option on the INFILE statement and SAS will not try to look for lines. Instead it will treat the file as one long stream.

So these lines are different than your previous examples. The spaces between TRIPLETS is missing sometimes. If the issue is that you either have a space or a semicolon between the triplets and not both then something like this should work.

data test;
  infile csv dlm=':/ ;' recfm=n;
  retain group 1;
  input area :$2. number value :commax. +(-1) sep $1. ;
  output;
  if sep=';' then group+1;
run;

For your new example file

options parmcards=csv;
filename csv temp;
parmcards4;
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;AD:1/205,92;DE:25/2039,05;
;;;;

This is the results

Obs    group    area    number      value     sep

 1       1       AD        1       1234.20
 2       1       AT        2      23456.35
 3       1       AU        3      34567.59
 4       1       CA        4      56789.05
 5       1       CH        5      54321.55     ;
 6       2       DE        3      34567.59
 7       2       ES       12       2341.25     ;
 8       3       AD        1        205.92     ;
 9       4       DE       25       2039.05     ;