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!
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.
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
@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 file
date 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;
I don't think your text file is delimited width semi-colon. I think it is delimited with TAB DLM='09'x
@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 file
date 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)
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?
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.
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;
A csv file is a text file, no need for conversion.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.