DATA Step, Macro, Functions and more

Import dates variable from CSV

Accepted Solution Solved
Reply
Contributor
Posts: 71
Accepted Solution

Import dates variable from CSV

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   erf8363 1997/3/11 0:00
135    1955/8/2 0:00erg4734 1997/3/11 0:00
132   erf620 1997/3/11 0:00
444   ergf9192 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 !


Accepted Solutions
Solution
‎07-05-2016 01:13 AM
Super User
Posts: 9,691

Re: Import dates variable from CSV

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

View solution in original post


All Replies
Solution
‎07-05-2016 01:13 AM
Super User
Posts: 9,691

Re: Import dates variable from CSV

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

Contributor
Posts: 71

Re: Import dates variable from CSV

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

Re: Import dates variable from CSV

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.

Super User
Posts: 9,691

Re: Import dates variable from CSV

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 .

Contributor
Posts: 71

Re: Import dates variable from CSV

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.

Super User
Super User
Posts: 6,502

Re: Import dates variable from CSV

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?

Contributor
Posts: 71

Re: Import dates variable from CSV

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 ?
Super User
Posts: 17,963

Re: Import dates variable from CSV

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

 

 

Super User
Posts: 10,552

Re: Import dates variable from CSV

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.

Super User
Posts: 17,963

Re: Import dates variable from CSV

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.

 

Contributor
Posts: 37

Re: Import dates variable from CSV

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 b c 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

 

 

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 439 views
  • 5 likes
  • 6 in conversation