This tutorial is easy to follow and provides good examples of how to tweak your import code. I recommend it for anyone learning about file manipulation and getting data into SAS.
Hello, thanks for this video, but I have got an issue with my csv database. I have line break among my items. Which command can I use to correct it please.
Honore, unfortunately, someone needs to look at your actual data and your code in order to help you with this. There are too many possibilities for how your CSV file is structured. And we cannot post code or screen shots in this CZcams feedback area. Your best resource for issues with your data and your program are either to open a track with Tech Support or to open a posting in the SAS Community Forums. To open a track with Tech Support, fill out the form at this link: 2.sas.com/6055Hcs0X . The Forum that is most relevant to you is the SAS Programming forum, here: 2.sas.com/6056Hcs0k . As an example of how other users encountered this same issue, here's a post: 2.sas.com/6057Hcs0Z on the SAS topic and also here: 2.sas.com/6058Hcs0w . As you can see, in these responses, the structure of the CSV file makes a difference in the coding solution and options you use.
J-H Mango, The short answer to your question is yes; but the longer answer is how you do it is dependent on several factors. You could, of course, write 20 separate PROC IMPORT steps. But, there are other methods to accomplish what you need to do. When you have multiple CSV files to import, how you do it will depend on several factors. For example, are the files named month1.csv, month2.csv, month3.csv, etc? Or, are the files named randomly: lucy.csv, kermit.csv, data123.csv? What about the CSV contents? Will you use the same options or same variable names for each file? Answering a question like this is outside the scope of this video. In this video, we demonstrated using PROC IMPORT to read both of the CSV files. As you saw in the video, each file required different options. You will need to review your 20 csv files to determine whether they will all use the same PROC IMPORT code or different PROC IMPORT code. You might even discover that it would be better to read the data with a DATA step program as might be the case if you have to read dates or times with special formats. Generally to perform repetitive processing, once you have working code, you can use the SAS Macro Facility and a SAS Macro Language program to generate repetitive code. We teach a bit about SAS Macro variables in our Programming 1 class, and we have 2 Macro Language courses. Here's a link to our Macro 1 class: 2.sas.com/605311BO5 .
what if my headername/column names is row 4 and data row is row 5. how can import the xlsx file. I have delcared the data row however struggling to get header names in row 4.
Thank you for the tutorial. I tried to read a csv dataset from a shared Drive L into SAS EG (V7.1) and I got an error saying below: NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to WORK.PARMS.PARMS.SLIST. ERROR: Physical file does not exist, /apps/sas/sasconfig/Lev1/SASApp/L:\A\Group\Lisa\airdata\air_annual.csv. ERROR: Import unsuccessful. See SAS Log for details." How can I fix the problem. Thanks
Hi there. Sorry to hear you are having trouble. Here are some common reasons why you could be seeing ERROR: Physical file does not exist. - Any part of the path or filename is misspelled - The directory or subdirectory does not exist - The drive may not be mapped to the correct location - Permission issues - Server issues - Special characters or spaces are used in the directory or filename where they are not supported Please double check these items. Also check out some tips from this blog post 2.sas.com/6059yhXtV If you are still having trouble, please email us at SAS_Cares@sas.com with your site number and a screenshot of the error.
Před 4 lety+1
Hello, What is the version of your SAS EG ? Thank You
Hi Peter , thanks for sharing importing csv file in to sas. I have a question ? Is there any other way to import cvs file into sas like importing excel files using libname statement with xlsx engine?
Thanks for your patience while we looked into this! Unfortunately, we can't post code or screen shots here in the CZcams feedback area. The various LIBNAME engines are provided using SAS/ACCESS for file types that are structured -- for example, files for Oracle, DB2, Microsoft Access, Microsoft Excel, SQL Server are all structured files for which SAS provides a LIBNAME engine. However, a CSV file is an unstructured type of ASCII text file. Excel knows how to open a CSV file by making every value between the commas into a column in a worksheet. But, a CSV file is basically unstructured. Sometimes the file has an extension of .CSV and sometimes, the file just has an extension of .TXT or .DAT and then you need to look inside the file using Notepad or Wordpad in order to see what delimiter is used to separate the variable values. The variable names may or may not be on the first row, the variable values may or may not be quoted. There are 2 ways to read a CSV file using SAS. Using PROC IMPORT is the first method we recommend that you try. PROC IMPORT usually does a fairly good job on importing a simple CSV file. PROC IMPORT can also import tab-delimited files or files with other delimiters, such as the pipe character | or even a semi-colon delimited file -- but you have to know what delimiter is used in those files so you can specify the correct delimiter in the IMPORT syntax. Eventually, with more complex CSV files, you may find you need more control over how the variables are imported into SAS format. In this case, you can get more control using a DATA step program. There's an example of writing a DATA step program to read a TXT file in this Community Forum posting: 2.sas.com/6052Kdni2 . And this forum would be a good place to seek advice and help from other SAS users on the pros and cons of using IMPORT over a DATA step. Hope that information helps!
Hi! We are sorry that our previous answer was not clear. No, you cannot use the LIBNAME engine to read a CSV file. For the reasons explained in our previous answer, you have 2 choices: 1) PROC IMPORT and 2) DATA step program.
Hi Abraham: SAS does not supply a macro to import multiple CSV files and merge them automatically as you describe. There may be some user-written macro programs to do the import for multiple files. However, for the merging part of your task, you may find that you have to write your own code. The best resource for questions like this is the SAS Community Forum. If you search in the Forums, you'll find useful postings like this one: 2.sas.com/60533pjGz and this one: 2.sas.com/60553pjG3 .
Hello, Rick! This video may be helpful for you: SAS Tutorial | Combining Data in SAS: DATA Step Versus SQL 2.sas.com/6058HXnP2. Be sure to click "SHOW MORE" in the description under that video to see all of the resource links there! We hope that helps!
Hello, could we still read a text file with all values in double quotes using proc import? or better to use data step infile statement? I mean can we use proc import to strip off all those double quotes?
That's a great question! When you use PROC IMPORT with a .csv file that has quotes around the data values, it will read the data and strip off the quotes before it stores the values in the data set. The challenge is, everything in quotes is considered a character column. Reading the .csv file with the DATA step will give you more control over the data column's type (character or numeric). You can email us at SAS_Cares@sas.com and we can send you a code sample.
@@SASUsers great, proc import can still strip off the quotes around the values. Data step has more control over the data lines/column type/column length. But proc import almost gives me everything to read various types of files as I can see from the log after running proc import, seems it is equivalent to infile fileref dsd dlm=',' missover lrecl=32327 firstobs=2; Thanks
@@SASUsers Hi, just wonder is there any better way to strip off the outer quotes around the string that has embedded double quotes, eg. "Brown, Mrs James Joseph (Margaret "Molly" Tobin)" , can we still read the inner quotes? Because I have tried with proc import and infile statement options, the string was truncated as "Brown, in Name field, so what is the best practice to deal with embedded double quotes? Thanks
Here are a few things to keep in mind when dealing with this situation. 1) Know your data. 2) Based on your data, keep in mind how you want to handle the data in certain situations. For this example, do you want to remove all the quotes, or just remove the outer quotation marks? The code you write depends on your answer. If you would like an example, please email us at SAS_Cares@sas.com
Happy to help. To clarify, Bruno, are you asking how to save your file? If so, this SAS Communities post shows how to save it to the permanent library: 2.sas.com/6051JqCJF Please let us know if you have any additional questions. You can also post your question and screenshots in the SAS Communities for additional help!
PDF is not a standard data exchange format, but you can use a tool like ghostscript to extract metadata and content from a PDF file, then import that into SAS. SAS Text Analytics tools also provide methods to extract unstructured text from documents. Hope that helps.
This tutorial is easy to follow and provides good examples of how to tweak your import code. I recommend it for anyone learning about file manipulation and getting data into SAS.
awesome tutorial easy to understand , thanks!
Thanks for your feedback, Mohammad - we're glad you liked it! 👍
Very helpful and easy to understand. Thank you so much 🙏
Glad it was helpful!
That was amazing)
Hello, thanks for this video, but I have got an issue with my csv database. I have line break among my items. Which command can I use to correct it please.
Honore, thank you for your inquiry! We are checking on this for you!
Honore, unfortunately, someone needs to look at your actual data and your code in order to help you with this. There are too many possibilities for how your CSV file is structured. And we cannot post code or screen shots in this CZcams feedback area. Your best resource for issues with your data and your program are either to open a track with Tech Support or to open a posting in the SAS Community Forums.
To open a track with Tech Support, fill out the form at this link: 2.sas.com/6055Hcs0X . The Forum that is most relevant to you is the SAS Programming forum, here: 2.sas.com/6056Hcs0k . As an example of how other users encountered this same issue, here's a post: 2.sas.com/6057Hcs0Z on the SAS topic and also here: 2.sas.com/6058Hcs0w . As you can see, in these responses, the structure of the CSV file makes a difference in the coding solution and options you use.
Thank you for sharing this video. May I know how to import more than 20 csv files at one time?
J-H Mango, The short answer to your question is yes; but the longer answer is how you do it is dependent on several factors. You could, of course, write 20 separate PROC IMPORT steps. But, there are other methods to accomplish what you need to do. When you have multiple CSV files to import, how you do it will depend on several factors. For example, are the files named month1.csv, month2.csv, month3.csv, etc? Or, are the files named randomly: lucy.csv, kermit.csv, data123.csv? What about the CSV contents? Will you use the same options or same variable names for each file? Answering a question like this is outside the scope of this video. In this video, we demonstrated using PROC IMPORT to read both of the CSV files. As you saw in the video, each file required different options. You will need to review your 20 csv files to determine whether they will all use the same PROC IMPORT code or different PROC IMPORT code. You might even discover that it would be better to read the data with a DATA step program as might be the case if you have to read dates or times with special formats. Generally to perform repetitive processing, once you have working code, you can use the SAS Macro Facility and a SAS Macro Language program to generate repetitive code. We teach a bit about SAS Macro variables in our Programming 1 class, and we have 2 Macro Language courses. Here's a link to our Macro 1 class: 2.sas.com/605311BO5 .
what if my headername/column names is row 4 and data row is row 5. how can import the xlsx file. I have delcared the data row however struggling to get header names in row 4.
Easy and understand Tutor oak
This is beautiful
I just shed some tears, not even kidding.
Thanks for sharing!
Thank you for the tutorial. I tried to read a csv dataset from a shared Drive L into SAS EG (V7.1) and I got an error saying below:
NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to
WORK.PARMS.PARMS.SLIST.
ERROR: Physical file does not exist, /apps/sas/sasconfig/Lev1/SASApp/L:\A\Group\Lisa\airdata\air_annual.csv.
ERROR: Import unsuccessful. See SAS Log for details."
How can I fix the problem. Thanks
Hi there. Sorry to hear you are having trouble.
Here are some common reasons why you could be seeing ERROR: Physical file does not exist.
- Any part of the path or filename is misspelled
- The directory or subdirectory does not exist
- The drive may not be mapped to the correct location
- Permission issues
- Server issues
- Special characters or spaces are used in the directory or filename where they are not supported
Please double check these items. Also check out some tips from this blog post 2.sas.com/6059yhXtV
If you are still having trouble, please email us at SAS_Cares@sas.com with your site number and a screenshot of the error.
Hello,
What is the version of your SAS EG ? Thank You
Michael, The current release is SAS Enterprise Guide 8.1 2.sas.com/60501Goki
Hi Peter , thanks for sharing importing csv file in to sas. I have a question ? Is there any other way to import cvs file into sas like importing excel files using libname statement with xlsx engine?
Hi! We're looking into this for you and will follow back up with more info! 👍
Thanks for your patience while we looked into this! Unfortunately, we can't post code or screen shots here in the CZcams feedback area. The various LIBNAME engines are provided using SAS/ACCESS for file types that are structured -- for example, files for Oracle, DB2, Microsoft Access, Microsoft Excel, SQL Server are all structured files for which SAS provides a LIBNAME engine.
However, a CSV file is an unstructured type of ASCII text file. Excel knows how to open a CSV file by making every value between the commas into a column in a worksheet. But, a CSV file is basically unstructured. Sometimes the file has an extension of .CSV and sometimes, the file just has an extension of .TXT or .DAT and then you need to look inside the file using Notepad or Wordpad in order to see what delimiter is used to separate the variable values. The variable names may or may not be on the first row, the variable values may or may not be quoted.
There are 2 ways to read a CSV file using SAS. Using PROC IMPORT is the first method we recommend that you try. PROC IMPORT usually does a fairly good job on importing a simple CSV file. PROC IMPORT can also import tab-delimited files or files with other delimiters, such as the pipe character | or even a semi-colon delimited file -- but you have to know what delimiter is used in those files so you can specify the correct delimiter in the IMPORT syntax.
Eventually, with more complex CSV files, you may find you need more control over how the variables are imported into SAS format. In this case, you can get more control using a DATA step program. There's an example of writing a DATA step program to read a TXT file in this Community Forum posting: 2.sas.com/6052Kdni2 . And this forum would be a good place to seek advice and help from other SAS users on the pros and cons of using IMPORT over a DATA step.
Hope that information helps!
thank you for reply. But I do not need code. can we import csv file using libname statement?
Hi! We are sorry that our previous answer was not clear. No, you cannot use the LIBNAME engine to read a CSV file. For the reasons explained in our previous answer, you have 2 choices: 1) PROC IMPORT and 2) DATA step program.
Thank you peter
Is there a macro to import multiple csv files into SAS and then merge them without writing each file name?
Abraham, thank you for your inquiry! We are checking on this for you!
Hi Abraham:
SAS does not supply a macro to import multiple CSV files and merge them automatically as you describe. There may be some user-written macro programs to do the import for multiple files. However, for the merging part of your task, you may find that you have to write your own code. The best resource for questions like this is the SAS Community Forum. If you search in the Forums, you'll find useful postings like this one: 2.sas.com/60533pjGz and this one: 2.sas.com/60553pjG3 .
How do you create a table using certain data from two different sas files.
Hello, Rick! This video may be helpful for you: SAS Tutorial | Combining Data in SAS: DATA Step Versus SQL 2.sas.com/6058HXnP2. Be sure to click "SHOW MORE" in the description under that video to see all of the resource links there! We hope that helps!
Hello, could we still read a text file with all values in double quotes using proc import? or better to use data step infile statement? I mean can we use proc import to strip off all those double quotes?
We are checking in to this for you!
That's a great question! When you use PROC IMPORT with a .csv file that has quotes around the data values, it will read the data and strip off the quotes before it stores the values in the data set. The challenge is, everything in quotes is considered a character column. Reading the .csv file with the DATA step will give you more control over the data column's type (character or numeric). You can email us at SAS_Cares@sas.com and we can send you a code sample.
@@SASUsers great, proc import can still strip off the quotes around the values. Data step has more control over the data lines/column type/column length. But proc import almost gives me everything to read various types of files as I can see from the log after running proc import, seems it is equivalent to infile fileref dsd dlm=',' missover lrecl=32327 firstobs=2; Thanks
@@SASUsers Hi, just wonder is there any better way to strip off the outer quotes around the string that has embedded double quotes, eg. "Brown, Mrs James Joseph (Margaret "Molly" Tobin)" , can we still read the inner quotes? Because I have tried with proc import and infile statement options, the string was truncated as "Brown, in Name field, so what is the best practice to deal with embedded double quotes? Thanks
Here are a few things to keep in mind when dealing with this situation.
1) Know your data.
2) Based on your data, keep in mind how you want to handle the data in certain situations. For this example, do you want to remove all the quotes, or just remove the outer quotation marks? The code you write depends on your answer. If you would like an example, please email us at SAS_Cares@sas.com
how can I store the result as a sas data file?
Happy to help. To clarify, Bruno, are you asking how to save your file? If so, this SAS Communities post shows how to save it to the permanent library: 2.sas.com/6051JqCJF
Please let us know if you have any additional questions. You can also post your question and screenshots in the SAS Communities for additional help!
Is It Possible to Import PDF Format files into SAS?
Great question. Looking into this for you. Are you using Base SAS or a different product?
PDF is not a standard data exchange format, but you can use a tool like ghostscript to extract metadata and content from a PDF file, then import that into SAS. SAS Text Analytics tools also provide methods to extract unstructured text from documents. Hope that helps.
@@SASUsers Thank You for Your Response. I am Using Base sas only
Hope the above information is helpful!
@@SASUsers Thank you for the Clarification. Its Very useful