topic Re: Import of csv in SAS Programming

Import of csv

SASdevAnneMarie — Thu, 10 Sep 2020 16:22:29 GMT

Hello Experts,

I can't import the attached file.

I don't know what is wrong with my code, before it worked well.

Now the data is completely wrong from the line 756 (if don't edit the file).

If I edit the file (even just delete the lines to make the file more light) the data is COMPLITELY wrong.

The original file is very huge, I can't download it, even with compress, I attached the edited file.


data EUROPERF_IMP1;
	dlm=';'||'0D0A'x;
	infile "XXXXXX\Eligibles_c.csv_20200810.csv" dlm=dlm  DSD lrecl=32867 /*termstr=CRLF*/
	RECFM=n;

	informat	CodeIsin	$12.;
	informat	Libelle	$50.;
	informat	nature	$12.;
	informat	categAMF	$32.;
	informat	categEPF	$50.;
	informat	zoneGeo	$40.;
	informat	dureeMinReco $8.;
	informat	affectationResultat	$40.;
	informat	fiscaliteText	$50.;
	informat	deviseComptable	$3.;
	informat	dateCloture	$32.;
	informat	dateOuverture	$32.;
	informat	promoteur	$50.;
	informat	adressePromoteur	$80.;
	informat	steGestion	$70.;
	informat	adresseSteGestion	$80.;
	informat	steAdmDelegue	$80.;
	informat	steFinAdmDelegue	$80.;
	informat	depositaire	$80.;
	informat	adresseDepositaire	$80.;
	informat	commissaireCpte	$50.;
	informat	souscription	$100.;
	informat	rachat	$50.;
	informat	fraisGestion	$70.;
	informat	datePerformance	$12.;
	informat	perf12mois	best32.;
	informat	perf36mois	$5.;
	informat	perf60mois	$5.;
	informat	fraisGestionCommentaire	$256.;
	informat	objectifGestion	$256.;
	informat	souscripteurs	$256.;
	informat	orientationPlacement	$256.;
	informat	dateClotureExercice	$256.;
	informat	periodeVL	$256.;
	informat	sitePublication	$256.;
	informat	conditionSouscripRachat	$256.;
	informat	dateValidation		$12.;
	informat	minimumRisqueAction	$5.;
	informat	dateAgrementAMF		$12.;
	informat	Tfe	$20.;
	informat	perfAm1	$8.;
	informat	perfAm2	$8.;
	informat	perfAm3	$8.;
	informat	perfAm4	$8.;
	informat	perfAm5	$8.;
	informat	perfAm6	$8.;
	informat	perfAm7	$8.;
	informat	perfAm8	$8.;
	informat	UnitedureeMinReco	$7.;
	informat	perfYTD	$5.;
	informat	volat_12mois	$5.;
	informat	frequence_text	$30.;
	informat	prospectus_url	$50.;
	informat	jour_valorisation $8.;
	informat	risque	$6.;
	informat	VIDE	$4.;
	format 	CodeIsin	$12.;
	format 	Libelle	$50.;
	format 	nature	$12.;
	format 	categAMF	$32.;
	format 	categEPF	$50.;
	format 	zoneGeo	$40.;
	format 	dureeMinReco $8.;
	format 	affectationResultat	$40.;
	format 	fiscaliteText	$50.;
	format 	deviseComptable	$3.;
	format 	dateCloture	$32.;
	format 	dateOuverture	$32.;
	format 	promoteur	$50.;
	format 	adressePromoteur	$80.;
	format 	steGestion	$70.;
	format 	adresseSteGestion	$80.;
	format 	steAdmDelegue	$80.;
	format 	steFinAdmDelegue	$80.;
	format 	depositaire	$80.;
	format 	adresseDepositaire	$80.;
	format 	commissaireCpte	$50.;
	format 	souscription	$100.;
	format 	rachat	$5.;
	format 	fraisGestion	$70.;
	format 	datePerformance $12.;
	format 	perf12mois	best32.;
	format 	perf36mois	$5.;
	format 	perf60mois	$5.;
	format 	fraisGestionCommentaire	$256.;
	format 	objectifGestion	$256.;
	format 	souscripteurs	$256.;
	format 	orientationPlacement	$256.;
	format 	dateClotureExercice	$256.;
	format 	periodeVL	$256.;
	format 	sitePublication	$256.;
	format  conditionSouscripRachat	$256.;
	format 	dateValidation		$12.;
	format 	minimumRisqueAction	$5.;
	format 	dateAgrementAMF		$12.;
	format 	Tfe	$20.;
	format 	perfAm1	$8.;
	format 	perfAm2	$8.;
	format 	perfAm3	$8.;
	format 	perfAm4	$8.;
	format 	perfAm5	$8.;
	format 	perfAm6	$8.;
	format 	perfAm7	$8.;
	format 	perfAm8	$8.;
	format 	UnitedureeMinReco	$7.;
	format 	perfYTD	$5.;
	format 	volat_12mois	$5.;
	format 	frequence_text	$30.;
	format 	prospectus_url	$50.;
	format 	jour_valorisation	$8.;
	format 	risque	$6.;
	format	VIDE	$4.;
	input			
		CodeIsin	$ 	
		Libelle	$	
		nature	$	
		categAMF	$	
		categEPF	$	
		zoneGeo	$	
		dureeMinReco $ 		
		affectationResultat	$	
		fiscaliteText	$	
		deviseComptable	$	
		dateCloture	$	
		dateOuverture	$	
		promoteur	$	
		adressePromoteur	$	
		steGestion	$	
		adresseSteGestion	$	
		steAdmDelegue	$	
		steFinAdmDelegue	$	
		depositaire	$	
		adresseDepositaire	$	
		commissaireCpte	$	
		souscription	$	
		rachat	$	
		fraisGestion	$	
		datePerformance	$
		perf12mois		
		perf36mois	$	
		perf60mois $		
		fraisGestionCommentaire	$	
		objectifGestion	$	
		souscripteurs	$
		orientationPlacement	$	
		dateClotureExercice	$	
		periodeVL	$	
		sitePublication	$	
		conditionSouscripRachat	$	
		dateValidation	$	
		minimumRisqueAction	$	
		dateAgrementAMF	$	
		Tfe	$	
		perfAm1	$	
		perfAm2	$	
		perfAm3	$	
		perfAm4	$	
		perfAm5	$	
		perfAm6	$	
		perfAm7	$	
		perfAm8	$	
		UnitedureeMinReco $		
		perfYTD	$	
		volat_12mois $		
		frequence_text	$	
		prospectus_url	$	
		jour_valorisation	$	
		risque 	$	
		VIDE $;
run;

Thank you for your help!

Best regards,

Marie

Re: Import of csv

jimbarbour — Thu, 10 Sep 2020 19:38:16 GMT

Marie,

My first reaction is that this code doesn't look right to me:

dlm=';'||'0D0A'x;

This code is saying that each column should be separated by three characters, taken together:

A semi-colon -- ';'
A carriage return -- '0D'x
A line feed -- '0A'x

When I look at the data, I only see the semi-colon between each field. I would change the delimiter setting to only the below and try re-running.

dlm=';'

Jim

Re: Import of csv

jimbarbour — Thu, 10 Sep 2020 20:02:14 GMT

I changed the program slightly as follows:

data EUROPERF_IMP1;
	infile "R:\users\jbarbou3\Eligibles_c.csv_20200810.csv" 
	MISSOVER
/*	termstr=CRLF*/
	lrecl=32867
	DSD 
	dlm=';'
	RECFM=n
	;

I ran the program, and the data looks like this:

Do you see the diagonal pattern in the data? Whenever I have seen a diagonal pattern like that in the past, that typically means that there is a mis-match between the number of columns defined in the INFILE vs. the number of delimiters (in this case a semi-colon) physically present in the data. I tried adding a MISSOVER parameter, but that did not help.

Has the data changed or been redefined in any way recently?

Jim

Re: Import of csv

Tom — Thu, 10 Sep 2020 20:31:11 GMT

Your file has multiple end of line characters inserted into the middle of the lines of data so not all lines have the same number of values.

data check;
 infile "&path/&fname" termstr=crlf;
 row+1;
 input;
 words=countw(_infile_,';','mq');
run;

proc freq ; tables words; run;

The FREQ Procedure

                                  Cumulative    Cumulative
words    Frequency     Percent     Frequency      Percent
----------------------------------------------------------
   16           1        0.82             1         0.82
   20           1        0.82             2         1.64
   29           1        0.82             3         2.46
   30           1        0.82             4         3.28
   31           5        4.10             9         7.38
   32           1        0.82            10         8.20
   33          22       18.03            32        26.23
   34          25       20.49            57        46.72
   35          11        9.02            68        55.74
   36           3        2.46            71        58.20
   43           1        0.82            72        59.02
   50           2        1.64            74        60.66
   52           1        0.82            75        61.48
   53           2        1.64            77        63.11
   54           4        3.28            81        66.39
   55          11        9.02            92        75.41
   56          30       24.59           122       100.00

There do not appear to be quotes around them or any other way to safely distinguish the spurios linebreaks from the real end of lines.

Can you have the file recreated without those characters?

Re: Import of csv

jimbarbour — Thu, 10 Sep 2020 21:38:32 GMT

@SASdevAnneMarie,

I would agree with @Tom that the best thing would be to have the data re-created with the following in mind:

The data should not include Line Feed ('0A'x) characters within the data.
The delimiters must consistently be a semi-colon.
The same number of delimiters must be on each line.
The line terminators should all be a combination of a Carriage Return ('0D'x) and a Line Feed ('0A'x) character (CRLF). The data is already correct as it is, but this must not be changed.

However, if it would be difficult to re-create the data, the data can be made to work. I made a few changes to the program and the data, and I got the following:

Notice that the data is now aligned properly.

Here is what I did:

First, the data sometimes contained Line Feed characters ('0A'x) as delimiters and sometimes the data contained semi-colons as delimiters. I changed all the solo Line Feed characters ('0A'x) to a semi-colon. Note that however each line is terminated by a combination Carriage Return ('0D'x) Line Feed ('0A'x). The combination must be preserved. In order to preserve the combinations, I first changed all the combinations ('0D0A'x) to '^^^'. Then I changed all the solo Line Feeds to a semi-colon. Then I changed all the '^^^' back to a combination Carriage Return Line Feed ('0D0A'x). It's actually easier that my description may make it sound. I did this in Notepad++ but any good editor should suffice.

Second, I changed the INFILE statement as follows:

	infile "XXXXXXXXXX\Eligibles_c.csv_20200810.csv" 
	termstr=CRLF
	lrecl=32767
	FIRSTOBS=2
	MISSOVER
	RECFM=V
	dlm=';'
	DSD 
	;

The important changes are:

The FIRSTOBS must be set to "2" so that the column headers are not treated as data.
The RECFM must be "V" (variable) not "N" (None, which implies streamed data).
The DLM must be ";" and only a semi-colon. CRLF should not be part of the the delimiter.
The TERMSTR must be CRLF.

I did change the LRECL slightly, but this is not too important as long as the LRECL is longer than the longest record. If you start getting truncation, you should increase this parameter.

So, in conclusion, I fully agree with @Tom that the best thing would be to go back to whoever created the data in the first place and have them re-create the data correctly. However, if you cannot reasonably have the data re-created, then you can change the solo Line Feed characters to semi-colons, make the program changes I described above, and the program will work correctly.

I hope this is helpful,

Jim

Re: Import of csv

SASdevAnneMarie — Thu, 10 Sep 2020 21:39:08 GMT

Thank you,
I tried this, but it doesn’t work:(

Re: Import of csv

jimbarbour — Thu, 10 Sep 2020 21:42:09 GMT

Yes, I now understand more, and you are correct. Please see my latest response, the very last one, below.

Jim

Re: Import of csv

Tom — Thu, 10 Sep 2020 21:42:09 GMT

If I edit the file (even just delete the lines to make the file more light) the data is COMPLITELY wrong.

Never open a CSV file with Excel. It can totally corrupt the data.

Re: Import of csv

SASdevAnneMarie — Thu, 10 Sep 2020 21:44:07 GMT

You mean that It would be better to openly the csv with Notepad?
Thank you!

Re: Import of csv

SASdevAnneMarie — Thu, 10 Sep 2020 21:46:38 GMT

Thank you very much!
You changed the line feeds on Notepad please?

Re: Import of csv

SASdevAnneMarie — Thu, 10 Sep 2020 21:48:17 GMT

I can’t ask to recreate the file:( Could we do this changes automatically please with SAS?
Thank you!

Re: Import of csv

Kurt_Bremser — Thu, 10 Sep 2020 21:48:40 GMT

Never (as in NEVER) inspect a CSV file with Excel. Use a programmer's editor like Notepad++ for this. Such editors keep the textual structure and will only do the changes you make.

Re: Import of csv

jimbarbour — Thu, 10 Sep 2020 21:48:47 GMT

I used Notepad++ not regular Notepad. There are probably other editors that would work as well, but I believe that Notepad++ is free.

Jim

Re: Import of csv

SASdevAnneMarie — Thu, 10 Sep 2020 21:50:08 GMT

Thank you!

Re: Import of csv

jimbarbour — Thu, 10 Sep 2020 21:50:26 GMT

You might need more than just regular Notepad. Regular Notepad will not show the Carriage Return and Line Feed characters. I used Notepad++.

Jim

Re: Import of csv

SASdevAnneMarie — Thu, 10 Sep 2020 21:53:44 GMT

Thank you!
Is it possible to make this changes automatically? I’ll receive this type of file for Input for my program.:(

Re: Import of csv

SASdevAnneMarie — Thu, 10 Sep 2020 22:01:31 GMT

Thank you!

Re: Import of csv

SASdevAnneMarie — Thu, 10 Sep 2020 22:04:05 GMT

Sorry Tom,
I’m curios)) Why Excel corrupts the csv? The encoding is not same?

Re: Import of csv

jimbarbour — Thu, 10 Sep 2020 23:18:05 GMT

Yes, these changes can be done with SAS. We would need two data steps.

The first Data step:

DATA	NULL;
	infile "XXX\Eligibles_c.csv_20200810_Original.csv"
	termstr=CRLF
	lrecl=32767
	RECFM=V
	;

	INPUT;
	_INFILE_	=	TRANWRD(_INFILE_, '0D0A'x, '^^^^');
	_INFILE_	=	TRANWRD(_INFILE_, '0A'x, ';');
	_INFILE_	=	TRANWRD(_INFILE_, '^^^^', '0D0A'x);
	file "XXX\Eligibles_c.csv_20200810_SAS_Edit.csv";
	PUT	_INFILE_;
RUN;

The first data step uses the original file, the file with the problems in the data. The first Data step corrects the problems.

The second data step then uses the file created by the first data step, above:

data EUROPERF_IMP1_SAS;
	infile "XXX\Eligibles_c.csv_20200810_SAS_Edit.csv"
	termstr=CRLF
	lrecl=32767
	FIRSTOBS=2
	MISSOVER
	RECFM=V
	dlm=';'
	DSD 
	;

	/*	56 Columns	*/
	informat	CodeIsin	$12.;
	informat	Libelle	$50.;
	informat	nature	$12.;
	informat	categAMF	$32.;
	informat	categEPF	$50.;
	informat	zoneGeo	$40.;
	informat	dureeMinReco $8.;
	informat	affectationResultat	$40.;
	informat	fiscaliteText	$50.;
	informat	deviseComptable	$3.;
	informat	dateCloture	$32.;
	informat	dateOuverture	$32.;
	informat	promoteur	$50.;
	informat	adressePromoteur	$80.;
	informat	steGestion	$70.;
	informat	adresseSteGestion	$80.;
	informat	steAdmDelegue	$80.;
	informat	steFinAdmDelegue	$80.;
	informat	depositaire	$80.;
	informat	adresseDepositaire	$80.;
	informat	commissaireCpte	$50.;
	informat	souscription	$100.;
	informat	rachat	$50.;
	informat	fraisGestion	$70.;
	informat	datePerformance	$12.;
	informat	perf12mois	best32.;
	informat	perf36mois	$5.;
	informat	perf60mois	$5.;
	informat	fraisGestionCommentaire	$256.;
	informat	objectifGestion	$256.;
	informat	souscripteurs	$256.;
	informat	orientationPlacement	$256.;
	informat	dateClotureExercice	$256.;
	informat	periodeVL	$256.;
	informat	sitePublication	$256.;
	informat	conditionSouscripRachat	$256.;
	informat	dateValidation		$12.;
	informat	minimumRisqueAction	$5.;
	informat	dateAgrementAMF		$12.;
	informat	Tfe	$20.;
	informat	perfAm1	$8.;
	informat	perfAm2	$8.;
	informat	perfAm3	$8.;
	informat	perfAm4	$8.;
	informat	perfAm5	$8.;
	informat	perfAm6	$8.;
	informat	perfAm7	$8.;
	informat	perfAm8	$8.;
	informat	UnitedureeMinReco	$7.;
	informat	perfYTD	$5.;
	informat	volat_12mois	$5.;
	informat	frequence_text	$30.;
	informat	prospectus_url	$50.;
	informat	jour_valorisation $8.;
	informat	risque	$6.;
	informat	VIDE	$4.;

	/*	56 Columns	*/
	format 	CodeIsin	$12.;
	format 	Libelle	$50.;
	format 	nature	$12.;
	format 	categAMF	$32.;
	format 	categEPF	$50.;
	format 	zoneGeo	$40.;
	format 	dureeMinReco $8.;
	format 	affectationResultat	$40.;
	format 	fiscaliteText	$50.;
	format 	deviseComptable	$3.;
	format 	dateCloture	$32.;
	format 	dateOuverture	$32.;
	format 	promoteur	$50.;
	format 	adressePromoteur	$80.;
	format 	steGestion	$70.;
	format 	adresseSteGestion	$80.;
	format 	steAdmDelegue	$80.;
	format 	steFinAdmDelegue	$80.;
	format 	depositaire	$80.;
	format 	adresseDepositaire	$80.;
	format 	commissaireCpte	$50.;
	format 	souscription	$100.;
	format 	rachat	$5.;
	format 	fraisGestion	$70.;
	format 	datePerformance $12.;
	format 	perf12mois	best32.;
	format 	perf36mois	$5.;
	format 	perf60mois	$5.;
	format 	fraisGestionCommentaire	$256.;
	format 	objectifGestion	$256.;
	format 	souscripteurs	$256.;
	format 	orientationPlacement	$256.;
	format 	dateClotureExercice	$256.;
	format 	periodeVL	$256.;
	format 	sitePublication	$256.;
	format  conditionSouscripRachat	$256.;
	format 	dateValidation		$12.;
	format 	minimumRisqueAction	$5.;
	format 	dateAgrementAMF		$12.;
	format 	Tfe	$20.;
	format 	perfAm1	$8.;
	format 	perfAm2	$8.;
	format 	perfAm3	$8.;
	format 	perfAm4	$8.;
	format 	perfAm5	$8.;
	format 	perfAm6	$8.;
	format 	perfAm7	$8.;
	format 	perfAm8	$8.;
	format 	UnitedureeMinReco	$7.;
	format 	perfYTD	$5.;
	format 	volat_12mois	$5.;
	format 	frequence_text	$30.;
	format 	prospectus_url	$50.;
	format 	jour_valorisation	$8.;
	format 	risque	$6.;
	format	VIDE	$4.;

	/*	56 Columns	*/
	input			
		CodeIsin	$ 	
		Libelle	$	
		nature	$	
		categAMF	$	
		categEPF	$	
		zoneGeo	$	
		dureeMinReco $ 		
		affectationResultat	$	
		fiscaliteText	$	
		deviseComptable	$	
		dateCloture	$	
		dateOuverture	$	
		promoteur	$	
		adressePromoteur	$	
		steGestion	$	
		adresseSteGestion	$	
		steAdmDelegue	$	
		steFinAdmDelegue	$	
		depositaire	$	
		adresseDepositaire	$	
		commissaireCpte	$	
		souscription	$	
		rachat	$	
		fraisGestion	$	
		datePerformance	$
		perf12mois		
		perf36mois	$	
		perf60mois $		
		fraisGestionCommentaire	$	
		objectifGestion	$	
		souscripteurs	$
		orientationPlacement	$	
		dateClotureExercice	$	
		periodeVL	$	
		sitePublication	$	
		conditionSouscripRachat	$	
		dateValidation	$	
		minimumRisqueAction	$	
		dateAgrementAMF	$	
		Tfe	$	
		perfAm1	$	
		perfAm2	$	
		perfAm3	$	
		perfAm4	$	
		perfAm5	$	
		perfAm6	$	
		perfAm7	$	
		perfAm8	$	
		UnitedureeMinReco $		
		perfYTD	$	
		volat_12mois $		
		frequence_text	$	
		prospectus_url	$	
		jour_valorisation	$	
		risque 	$	
		VIDE $;
run;

Why don't you try this code and let me know if it works?

Jim

Re: Import of csv

Tom — Thu, 10 Sep 2020 22:15:09 GMT

@SASdevAnneMarie wrote:
Thank you!
Is it possible to make this changes automatically? I’ll receive this type of file for Input for my program.:(

You need to talk to the source for this file and see if they can adjust there process to produce a file that can be parsed. Find out what software they are using to create the file and perhaps there are simple changes in how they create the file that could eliminate this issue.

Ask them to remove any carriage return ('0D'x) or linefeed ('0A'x) values from the data fields. They can either just strip them out or replace them with some other character(s). Make sure that fields are separated by the same delimiter. And that any value that contains the delimiter is enclosed in quotes. Any values that contain quotes are also enclosed in quotes and the existing quotes are doubled up.

You could try to recreate the manual step of replacing linefeeds with semi-colons.

filename copy temp;
data _null_;
  infile "&path/&fname" termstr=crlf ;
  input;
  _infile_=translate(_infile_,';','0A'x);
  file copy;
  put _infile_;
run;

But when I do that I notice that some of the lines are close to the limit of 32K bytes that can be handled by the _INFILE_ automatic variable.

     The minimum record length was 717.
     The maximum record length was 32489.

So you might need a more complicated process instead that reads the file character by character instead.