DATA Step, Macro, Functions and more

Problem to infile a csv file (beginner)

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Problem to infile a csv file (beginner)

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

 

Capture2.JPG

 

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 : 

 

 

 Capture.JPG

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)

 

cap3.JPG

 

AND

 

cap4.JPG

 

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

 

cap5.JPG

 

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

 

cap6.JPG

 

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.


Accepted Solutions
Solution
‎11-30-2016 08:31 AM
Super User
Posts: 17,784

Re: Problem to infile a csv file (beginner)

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;

 

 

View solution in original post


All Replies
Super User
Posts: 6,932

Re: Problem to infile a csv file (beginner)

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 10,483

Re: Problem to infile a csv file (beginner)

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.

Solution
‎11-30-2016 08:31 AM
Super User
Posts: 17,784

Re: Problem to infile a csv file (beginner)

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;

 

 

New Contributor
Posts: 3

Re: Problem to infile a csv file (beginner)

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 :

 

Capture12.JPG

 

At first, on Excel, it is :

 

capture1212.JPG

 

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 :

 

log.JPG

 

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

Super User
Posts: 6,932

Re: Problem to infile a csv file (beginner)

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
New Contributor
Posts: 3

Re: Problem to infile a csv file (beginner)

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

 

12capture.JPG

 

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.

Super User
Posts: 6,932

Re: Problem to infile a csv file (beginner)

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is SOLVED.

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

Discussion stats
  • 7 replies
  • 312 views
  • 3 likes
  • 4 in conversation