Problem to infile a csv file (beginner)

[Louliloul]

Hi everyone,

I started SAS a few days ago and i have some trouble to infile a csv file. I try to do exactly as the video of the free video tutorials on the SAS website, but this doesn't work. Of course, i searched before posting my message, but the anwsers didn't work, or were with a code really complex for my level, that i couldnt really manage. 

I work on SAS 9.3

I created a library named sashimi in wich i wanted to create a file named score, corresponding with a csv file, exactly as this video :

http://support.sas.com/training/tutorial/studio/create-table-csv-file.html

First i didn't put the input command because there are a lot of columns in the file. So i tried without, and they put this : 

 

So i tried to test to put a few columns adding the command 

input numero_anonymisation age;

So just two columns of the file.

  And i have this error (i put the beginning and the end only)

AND

So i decided to create a new csv file with only a few variables

data testtt;
infile 'C:\Users\LOTFI\Desktop\test.csv' dlm=';' firstobs=2;
input sexe $ age taille;
run;

proc print data=testtt;
run;

And this work

So i guess this is a problem of inputting. So do i have to put every columns if i do this code, or there is a command to simplify this ? Or if i want to input only a few, is there something i could do ? Because i didnt find it on forums or tutorials.

[Kurt_Bremser]

Inspect your csv file with a text editor to get a feel for the record length. You will have to add a suitable lrecl= option to the infile statement.

The lrecl for a standard (variable length) text file should be selected sufficiently larger than the longest line (to have a reserve in case the records grow larger in the future).

Also make sure that the end-of-line sequence is right for your operating system (should be CRLF(Windows) and not only LF(UNIX)).

[ballardw]

I would recommend using the data import wizard from the File menu. The steps will guide you through some of the options and generate proc import code. The part to look at is that when the proc import runs it generated data step code. You can copy that from the log or Recall it into the editor and modify if needed. As a minimum it will likely show you some details you missed.

If the data set does not have column headers you will end  up with var1, var2 ... varN for variable names. If you have a separate document for the variable names you may be able to use some editor tricks to get things that you need.

[Reeza · Accepted solution]

Welcome to SAS. I was going to modify your code to a proc import, but it's an image....please post as text in the future, not images. 

I would recommend a standard proc import. Then you can look at your log and modify as necessary.

Your proc import should be along the lines of:

proc import out=WANT datafile="path to your csv" dbms=csv replace;
getnames=yes;
run;

[Louliloul]

Hi guys, thanks for the replies !

Good news : with your help and a simple code, i am now able to import a excel file just coding it with this code :

proc import out=testitest 
	datafile='C:\Users\LOTFI\Desktop\testguigui.xlsx';
run;

It works, and i would not be able to do it without asking my questions here, so thanks.

Bad news : if i want to import a csv file (file ==> Import => etc... OR coding it), i have this kind of thing :

At first, on Excel, it is :

When i code it :

proc import out=testitest 
	datafile='C:\Users\LOTFI\Desktop\test.csv';
run;

I try to add dlm=';' in different positions in this code, because i guessed maybe there a problem with that, but even if i dont code it and if i'm using the way with File==>Import ect..., it's the same.

Here is the log window when i code it :

Is it because when i change a excel file into a csv file, i have to do something special so it is recognized by SAS ?

 Anyway, thanks again for you quick help. I could not answer as quickly as you did but i knew i would go back here ^^

Cheers

[Kurt_Bremser]

DBMS=CSV indicates a Comma Separated Values file, so you should use a comma as delimiter when creating the file (and not a semicolon).

Or use DBMS=DLM:

proc import out=test datafile='$HOME/test.csv' dbms=dlm replace;
getnames=yes;
delimiter=';';
run;

[Louliloul]

Your code work and the table on sas is normal. On the log they show errors 

But if the table is clean, i guess it's not a problem.

I'm not sure to understand the comma and semicolon thing but I'm not sure it's important, as long as the code you gave me works.

Another thing i don't really understand (mostly becasue i'm beginning) is why sometimes you have to put the semicolon, and why sometimes you dont. Here use the semicolon only after replace, why not before ? 

And where to put the command firstobs=... because if i add this command, the table is not created.

Thanks for the anwsers anyway ^^

Cheers.

[Kurt_Bremser]

I don't see ERRORs, only NOTEs, which all indicate that the code worked.

out=, datafile=, dbms= and replace are all options of the proc import statement. getnames= and delimiter= are separate statements within the proc import procedure.