topic Import csv with all data in one column and separate into columns in SAS Programming

Import csv with all data in one column and separate into columns

mariapf — Tue, 10 Nov 2020 10:23:21 GMT

Hey all,

The data that I need to work with is provided to me in a csv that contains all data in a single column of the csv. I can easily separate the data into columns in excel (thus obtaining all of the variables I need), but I need to find a way for SAS to separate the data into columns.

My csv contains in one column all data in this format:

month,year,country,cashflow

06,2018,Germany,23754

12,2011,France,567890

02,2003,Poland,788229

And I need to import the data into SAS so that it looks like this (without separating the columns in excel first):

month year country cashflow

06 2018 Germany 23754

12 2011 France 567890

02 2003 Poland 788229

Does anyone know how to do this directly in SAS? Thank you in advance

Re: Import csv with all data in one column and separate into columns

Kurt_Bremser — Tue, 10 Nov 2020 11:00:23 GMT

Very simple:

data want;
infile datalines dlm="," dsd truncover;
input month year country :$20. cashflow;
datalines;
06,2018,Germany,23754
12,2011,France,567890
02,2003,Poland,788229
;

Replace the DATALINES option of the INFILE statement with the path to your CSV file.

Re: Import csv with all data in one column and separate into columns

Tom — Tue, 10 Nov 2020 16:41:32 GMT

I am confused by your question. The file you posted looks like a normal CSV (comma separated values) file. Why do you say it has data in ONE column?

Re: Import csv with all data in one column and separate into columns

mariapf — Mon, 16 Nov 2020 14:16:01 GMT

you are right! there was some confusion on my part since I am just a beginner in sas programming