Hello everybody,
I have a sample of data which is attached. I want to do the following tasks:
1. Import thousands of CSV files similar to the sample (with the same format). Then merge files into a single SAS file. I want a macro to import and merge files.
2. Translate variable T_connected and Tenure_firm into numbers (or create new variables based on variable T_connected and Tenure_firm ). In this case, I suppose the translated value is 0 for T_connected; and 2 and 0 for Tenure_firm
3. Translate variable Startdate into a variable with year only. In original files, the Startdate variable sometimes appears with year only, sometimes with month and year, sometimes with date month and year, sometimes it states "current" (current year is 2014). I want a numeric variable with year only (e.g. 2012).
Could anyone can help me on this issue? Please suggest codes which particularly fit my data. I really appreciate your help. Thank you very much!
Cheers, Thierry
Your attachment is not a csv. Please post a sample from the csv as text (use the {i} icon).
Hi, it is an excel file. The web does not allow to attach CSV files. But you can convert it to CSV file easily.
Rename the csv to txt. Many (most) members refrain from downloading xlsx files which are considered unsafe.
Also, please tell us how the data files are organized within your directory structure. Can they be listed with a simple wildcard name such as c:\myFiles\*.csv ?
Here's instructions on how to read a bunch of CSV files that all have the same format.
If they're not stored in the same folder look at the FILEVAR option on the INFILE statement. I'm not about to do all of your work/homework so give it a shot first and ask questions that are more specific than your entire problem.
The picture of sample is attached. Thanks
Upload file as txt instead of CSV, Excel can change the data inside a CSV so it's not a good transfer mechanism.
Images are not helpful, since we won't take the time to type out the data.
Thank you very much for your responses. Here is the txt file. All files are stored in the same folder (in my PC). Thank you very much!
Best wishes. Thierry.
The approach suggested by @Reeza should work for you, except that you should add option DLM='09'x to your INFILE statement, as your data seems to be TAB delimited and not comma delimited.
@tritringuyen wrote:
I need codes as detailed as possible.
Then you hire a consultant or do the research and at least make an attempt. There are several programmers here for hire if you're interested.
Have you tried the solution I proposed?
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.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.