Hello,
When I use 'proc import' to import csv files into SAS some observations become missing (these are not missing in the original csv files). Each csv file has 2474 rows and 2466 columns. It is several observations in the bottom right of the excel table that becomes missing.
Below is the commands I use.
I am trying to import 14 csv files by using a macro (data starting on row 3), and some missing observations (in the bottom right of the table) appears in all 14 of them.
It is not reported anything about this in the log.
%macro readdat;
%do i = 2000 %to 2014;
PROC IMPORT datafile="/folders/myfolders/WIO&i..csv"
OUT=WIO&i
DBMS=DLM
REPLACE;
DELIMITER='2C'x;
Getnames=NO;
Datarow=3;
RUN;
...
%end;
%mend readdat;
%readdat;
What could be the problem? Help is very appreciated!
Best,
Birgitte Ringstad
Why would you ask PROC IMPORT to guess what you data looks like if you know that there are 2466 variables already?
You can read the data for all 15 years into a single dataset much easier just using a data step.
data want ;
length fname $200 ;
do year = 2000 to 2014;
fname = cats("/folders/myfolders/WIO",year,".csv");
infile csv filevar=fname dsd dlm=',' firstobs=3 truncover end=eof;
do while (not eof);
input var1-var2466 ;
output;
end;
end;
stop;
run;
If your 2466 variables are not all numeric then just update the INPUT statement to reflect what they actually are.
Inspect the csv file with a proper editor (preferred one that can display in hex format, so you can find "interesting" characters).
If you use dbms=csv, you can omit the delimiter= statement (csv automatically uses a comma as delimiter).
If inspecting the file does not give you a clue, post the relevant portion of the file.
Tip 1 - CSV (Comma Separated Variable text file) is not the same as an Excel file. Opening a CSV (or in fact any file) in Excel can invoke many Excel "features" where data is hidden, displayed differently or any number of other nice features. Use a plain text editor to see the real file.
Tip 2 - CSV file format indicates row 1 contains variable names. From row 2 onwards is data. If your "data" starts on row 3 then it is not a CSV file.
Tip 3 - Proc import is a guessing procedure. It does not know your data. It runs through some observations and makes a best guess on what that data is/contains. If the guess is wrong you can end up with odd results.
Tip 4 - You can import multiple files using infile in a datastep (and fix tip 2 also) no need to do any macro processing. Here is result 1 of a google on the subject:
http://www.ats.ucla.edu/stat/sas/faq/multi_file_read.htm
Tip 5 - Putting data (in this case) into dataset names, or dataset variable names, will do several things. It will first make your code vastly more complicated than it needs to be. It will also be harder to maintain, i.e. less robust, and be less efficient. Using the indsname option you can create a varible for date - so a proper date variable - and put all the data into one dataset - i.e no looping, need to know dataset names etc.
Why would you ask PROC IMPORT to guess what you data looks like if you know that there are 2466 variables already?
You can read the data for all 15 years into a single dataset much easier just using a data step.
data want ;
length fname $200 ;
do year = 2000 to 2014;
fname = cats("/folders/myfolders/WIO",year,".csv");
infile csv filevar=fname dsd dlm=',' firstobs=3 truncover end=eof;
do while (not eof);
input var1-var2466 ;
output;
end;
end;
stop;
run;
If your 2466 variables are not all numeric then just update the INPUT statement to reflect what they actually are.
If I execute your job in my SAS environment then I get into an infinite loop if I don't add a "stop;" right before the "run;"
That makes sense that the STOP is required. Normally SAS stops a data step when it reads past the end of the input file, but in this case the inner loops are prevented from reading past the end and the outer loop gets its data from the DO loop instead of a SET statement. So the STOP is required.
Thank you.
I tried this code now, including STOP; right before END;, and it works okey.
My first two variables are character variables, how do I specify that in the code?
Birgitte
Change the INPUT statement.
Normally I like to define the variable first. You can use a LENGTH statement to define the type and length of the variables. You can even give them names that make sense if you want. If you do you could change the variable list in the INPUT statement to use a positional list of names instead of a simple sequential list of names by using double hyphen .
length ID $20 NAME $30 var3-var5678 8 ;
input id -- var5678 ;
STOP would go after the END statement or else it will only do one file.
Put the STOP before the RUN statement.
Thank you!
I did it like this now, so far it works.
data WIO;
length fname $200 ;
do year = 2000 to 2014 ;
fname = cats("/folders/myfolders/WIO",year,".csv");
infile csv filevar=fname dsd dlm=',' firstobs=3 truncover end=eof;
do while (not eof);
length ctr $20 sec $30 var3-var2466 8;
input ctr -- var2466;
output;
end;
end;
stop;
run;
The two character variables are in column 1 and 2, their names can be ctr and sec, and the rest of the variables are numeric variables. What does the $20, $30 and 8 indicate?
And also the $200 specification in the second line?
The log notes that the minimum length for each dataset was 27108 and the maximum record length was 32767 for each of the imported csv files. Should these two lengths be the same?
LOG example (it says the same for each of the 15 imported csv files):
NOTE: The infile CSV is:
Look up the LENGTH statement in the man pages. SAS has two types of variables floating point numbers and fixed length character strings. The $ indicates the variable is a string and the length is the maximum number of bytes it can store. For numbers it is possible to request SAS to use less than the full 8 bytes that it takes to store a floating point number, but it is not worth it.
The default LRECL for INFILE is 32767. If you are seeing that as the maximum length read then most likely some of your lines have been truncated. Run it again but add the LRECL= option to the INFILE statement and specify a longer length such as 1000000.
It is surprising that 32,767 is not enough space to store 2463 numbers. That would imply that your average number is more than 10 digits long. Perhaps you have floating point numbers with too many digits of precision?
Yes, I do have 8 digits after comma on all the variables, could that cause a problem?
The maximum length seem to have changed after including the LRECL alternative in the code. The minimum length is the same for each of the 15 files, and the maximum code varies.
Another question:
If I already have names for each column in the csv file (say in the first row), would it be possible to use these names for the variables in the dataset in sas? How could I include this in the code?
(Before I have used GETNAMES when using the the PROC IMPORT statement, but here I could not make it work)
Sorry for all the questions, but I am a bit desperate after using days on this.
Thanks a lot.
Good to know, thank you!
If I have a row (say row 1) with names for all the numeric variables (var3-var2466), and I would like to include these when I import the data from the csv files, how could I include that in the code?
The names are like this: AUS, AUS_1, AUS_2, AUS_3, AUS_4, AUT, AUT_1, AUT_2, AUT_3, AUT_4 and up to ZOW, ZOW_1, ZOW_2, ZOW_3, ZOW_4. So there are 5 variables for each of 44 countries, summing to 220.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: