topic Re: Import dates variable from CSV in SAS Programming

Import dates variable from CSV

deega — Tue, 05 Jul 2016 05:00:33 GMT

Hi,

I am trying to import a CSV file in SAS Base 9.4. I have tried both Proc import and import wizard but I am getting just '1' in the place of dates and by default its taking text format. I tried format but I get error saying I can not use format.

The data in csv is something like this( this is dummy example just for reference). Funniest part is SAS is interpreting the last date column correctly but not the first one.


132		erf	8363	1997/3/11 0:00
135	1955/8/2 0:00	erg	4734	1997/3/11 0:00
132		erf	620	1997/3/11 0:00
444		ergf	9192	1997/3/11 0:00

Apart from import wizard I used following code

proc import datafile = "Chjkj.csv"
out = c11
dbms = csv
replace;
getnames = yes;
run;

Thanks in advance !

Re: Import dates variable from CSV

Ksharp — Tue, 05 Jul 2016 05:10:19 GMT

proc import datafile = "Chjkj.csv"
out = c11
dbms = csv
replace;
getnames = yes;
guessingrows=32767;
run;

Re: Import dates variable from CSV

Reeza — Tue, 05 Jul 2016 05:15:10 GMT

Also, when you use proc import look at your log. It has the data step code. Copy that to your editor and edit the fields that aren't being imported properly.

You can copy the code without line numbers by pressing ALT+highlighting the code without line numbers.

Re: Import dates variable from CSV

deega — Tue, 05 Jul 2016 05:15:26 GMT

Wow ! Its working.... I wasted so many hours in this.....Thank you so much !
But

Re: Import dates variable from CSV

LittlesasMaster — Tue, 05 Jul 2016 05:19:46 GMT

Hey,

your code does not have problem, tried same and working fine.

Check if all your sas components are installed correctly i.e SAS/ACCESS and SAS/CONNECT

proc import datafile = "C:\Users\ak26\Desktop\Book1.csv"
out = c11
dbms = csv
replace;
getnames =yes;
run;
proc print;run;

SAS Output

The SAS System

Obs	A	d	e	f	g
1	132	.	erf	8363	03/11/1997
2	135	08/02/1955	erg	4734	03/11/1997
3	132	.	erf	620	03/11/1997
4	444	.	ergf	9192	03/11/1997

Re: Import dates variable from CSV

deega — Tue, 05 Jul 2016 06:05:21 GMT

Is there any solution for this problem also....

even after using getnames=yes, my SAS table does not take the variable names from CSV file, instead it names it VAR1 VAR2... and so on.

Re: Import dates variable from CSV

Ksharp — Tue, 05 Jul 2016 07:02:51 GMT

That is really weird. is your data starting from first row ?

Try option:

datarow=3;

Better post your sample data to let us test it .

Re: Import dates variable from CSV

ballardw — Tue, 05 Jul 2016 15:13:29 GMT

GETNAMES assumes the variable names are on the first row. If for some reason they aren't then you'll get that behavior and possibly some variables that should be numeric end up character as the name is treated as a data value.

Also a good diagnostic when using proc import for delimited files is the log. It will contain the datastep code generated to read the data.

Sometimes it is best to copy the code from the log and modify to your needs such as setting consistent lengths for similar variables, especially if you are going to combine datasets, spellings of variable names and it often is a good idea to add variable labels.

Re: Import dates variable from CSV

deega — Wed, 06 Jul 2016 02:57:24 GMT

I have found the reason of this weird behaviour. My Variable names are in Japanese, my program read it correctly when I changed them in source CSV. However, even the Japanese version of SAS is not reading the Japanese fonts and naming them as var1 var2 ... and so on. If you have any idea of how to solve this problem, please tell me.

Re: Import dates variable from CSV

Tom — Wed, 06 Jul 2016 03:08:41 GMT

How was the CSV file created? What encoding was used? Did SAS properly detect the encoding? Are the characters consistent with the encoding?

Can you post an example file with 2 or 3 columns and 3 or 4 rows?

Re: Import dates variable from CSV

deega — Fri, 08 Jul 2016 06:59:37 GMT

CSV files are not created at my end. I get data from the client in the form of CSV files. Where can I find SAS encoding ?

Re: Import dates variable from CSV

Reeza — Fri, 08 Jul 2016 07:14:11 GMT

Assuming you know the encoding you can specify it on the infile statement.

Heres a link on how to find your encoding.

http://programmers.stackexchange.com/questions/187169/how-to-detect-the-encoding-of-a-file