date text file

[always-good]

Hello everyone,

I want to ask how to correctly convert a CSV file containig date formate to a text file. Because when I convert it, the variables are not arranged in the correct way.

I will infile then this text file with date formate into SAS.

Thank you!

[PaigeMiller]

Because when I convert it, the variables are not arranged in the correct way.

I don't know what this means. Please show us a portion of the data, please show us your SAS code, and please show how your SAS code doesn't produce the desired results

[always-good]

---

@PaigeMiller wrote:

Because when I convert it, the variables are not arranged in the correct way.

I don't know what this means. Please show us a portion of the data, please show us your SAS code, and please show how your SAS code doesn't produce the desired results

---

I mean the CSV file is like in screenshot 1  and the text file obtained is in the second screenshot 2

date excel filedate text file

and to infile the text file in SAS I used this code:

filename BSMB 'C:\Users\x\Desktop\BSMB\text.txt';
data farm;
infile BSMB firstobs=2 dlm=';';
input Period THIP Month	THIIM ID	breed Parity DIM Milk  TOTALMilk 	FAT	 FM  	PROTEIN  PM  SC	SCS	DP GP Nins IM LastI CD	PC di met est	pro  CCI	ICIf	RT	HR 	RR 	PS
informat LastI CD	PC di met est	pro ddmmyy.;
  format LastI CD	PC di met est	pro yymmdd10.;
run;

[data_null__]

I don't think your text file is delimited width semi-colon.  I think it is delimited with TAB DLM='09'x

[always-good]

Yes.Thank you!

[ballardw]

---

@always-good wrote:

---

I mean the CSV file is like in screenshot 1  and the text file obtained is in the second screenshot 2

date excel filedate text file

That is not a screen shot of a CSV. I would place a not-so-small wager that you have the file opened in a spreadsheet program. Which will make assumptions and display values other than as they appear in a CSV file. Plus if you saved the file on closing then the content may well have changed.

A CSV is TEXT and looks like this if comma separated:

Project ID,FT Eload,Surg Med,Surg Med Proc,Metric,Date scaf,Domain
ConnectCare_014,H2,Surgery,Surgery,Time in In Basket per Day,10/31/2020,In Basket
ConnectCare_014,H2,Surgery,Surgery,Time in In Basket per Day,10/30/2020,In Basket
ConnectCare_014,H2,Surgery,Surgery,Time in In Basket per Day,10/29/2020,In Basket
ConnectCare_014,H2,Surgery,Surgery,Time in In Basket per Day,10/28/2020,In Basket
ConnectCare_014,H2,Surgery,Surgery,Time in In Basket per Day,10/27/2020,In Basket
ConnectCare_014,H2,Surgery,Surgery,Time in In Basket per Day,10/26/2020,In Basket
ConnectCare_029,H1,Medical,Procedure,Time in In Basket per Day,6/1/2021,In Basket
ConnectCare_029,H1,Medical,Procedure,Time in In Basket per Day,5/31/2021,In Basket
ConnectCare_029,H1,Medical,Procedure,Time in In Basket per Day,5/30/2021,In Basket
ConnectCare_029,H1,Medical,Procedure,Time in In Basket per Day,7/31/2021,In Basket

If the separating character is other than a comma then replace the commas above with that character.

Open your CSV file in a TEXT editor, such as Notepad or even the SAS editor. Copy some text then on the forum open a text box and paste that to show what your CSV looks like.

You may also need to share the LOG from when you read that file. If there are actually values that should be read with a DDMMYY10. informat and you use DDMMYY., which will default to 6 characters, then the / make the first 6 characters and invalid date: 01/03/ (6 characters and no year to read)

[always-good]

Thank you for your answer.

After many trials, I obtained this text file like in screenshot 3 

I runned the following code :

filename BSMB 'C:\Users\x\Desktop\BSMB\calv.txt';
data farm;
infile BSMB firstobs=2 dlm='09'x;
input ID Breed Parity DIM IM PI date10. I date10. INS date10. PC date10. CD date10. IF date10. Nins CIFI CCI;
run;

But it is mentionned an invalid data in the log.

How can SAS read the file please?

[Kurt_Bremser · Accepted solution]

You need to use the DDMMYY10. informat. The DATE informat expects a date to be written like 01jan1960.

And don't forget to use the colon modifier in front of the informats, otherwise list input won't work.

[always-good]

I would like to thank all of you 💐

I successfully ran the code that I would  share here  It was really helpful and I appreciate it.

Thanks again!

filename BSMB 'C:\Users\X\Desktop\BSMB\calv.txt';
data farm;
infile BSMB firstobs=2 dlm='09'x;
input ID Breed Parity DIM IM PI  I  INS  PC  CD  IF  Nins CIFI CCI;
informat PI  I  INS  PC  CD  IF ddmmyy.10;
format PI  I  INS  PC  CD  IF yymmdd10.;
run;

[Kurt_Bremser]

A csv file is a text file, no need for conversion.