BookmarkSubscribeRSS Feed
tritringuyen
Quartz | Level 8

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

22 REPLIES 22
tritringuyen
Quartz | Level 8

Hi, it is an excel file. The web does not allow to attach CSV files. But you can convert it to CSV file easily.

PGStats
Opal | Level 21

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 ?

PG
Reeza
Super User

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. 

 

https://communities.sas.com/t5/SAS-Communities-Library/How-do-I-write-a-macro-to-import-multiple-tex...

tritringuyen
Quartz | Level 8

The picture of sample is attached. Thanks


Untitled.png
Reeza
Super User

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. 

tritringuyen
Quartz | Level 8

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.

PGStats
Opal | Level 21

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.

PG
ballardw
Super User

@PGStats wrote:

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.


And looks like some form of double-byte character set as well.

tritringuyen
Quartz | Level 8
Hi Ballardw, thank you very much, but I dont really get what you mean. 😞
tritringuyen
Quartz | Level 8
Thank you PG. They are CSV files. Although your suggestion should be valuable. However, I am still cannot process further with @Reeza codes. I need codes as detailed as possible.
Reeza
Super User

@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. 

tritringuyen
Quartz | Level 8
Dear Reeza, Thank you very much for your code. I suppose that i should work. However, SAS is quite new for me. I wonder if you could more specific about "*Otherwise go to the import step and read the files;
else input

*Place input code here;".

Actually, I dont know how to process further. Thank you very much!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

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.

Discussion stats
  • 22 replies
  • 5491 views
  • 10 likes
  • 9 in conversation