06-17-2015 09:48 PM
I have an excel file with 5 sheets that I saved as separate CSV files. Was able to import them but some of variables were renames as VAR"Number" rather than the real name and a proc contents revealed that the numeric values were formatted as Num / Char. I tried to save the excel sheets with the numeric values saved as numeric but could not do this with CSV.
Here is an example from the proc contents:
Variable type format informat
Liver "\\psf\Home\Desktop\Liver Rascal\CSV_Sheets";
IMPORT OUT= Liver.Sheet_1
DATAFILE= "\\psf\Home\Desktop\Liver Rascal\CSV_Sheets/Sheet_1.csv"
IMPORT OUT= Liver.Dead_Sheet2
DATAFILE= "\\psf\Home\Desktop\Liver Rascal\CSV_Sheets\Dead_Sheet2.csv"
temp '\\psf\Home\Desktop\Liver Rascal\CSV_Sheets\Sheet_3.csv' termstr=cr lrecl=32767;
import datafile=temp out=Liver.Sheet_3 dbms=csv replace;
IMPORT OUT= Liver.tenin24h_Sheet_4
DATAFILE= "\\psf\Home\Desktop\Liver Rascal\CSV_Sheets\tenin24h_Sheet_4.csv"
IMPORT OUT= Liver.MRN_Na_10_in_24h_Sheet_5
DATAFILE= "\\psf\Home\Desktop\Liver Rascal\CSV_Sheets\MRN_Na_10_in_24h_Sheet_5.csv"
sort data= Liver.Sheet_1;
sort data= LIVER.Dead_Sheet2;
sort data= Liver.Sheet_3;
sort data= Liver.tenin24h_Sheet_4;
sort data= Liver.MRN_Na_10_in_24h_Sheet_5;
Liver.Sheet_1 LIVER.Dead_Sheet2 Liver.Sheet_3 Liver.tenin24h_Sheet_4 LIVER.MRN_Na_10_in_24h_Sheet_5;
I've attached what I have done so far with the code. Seem to have all the data in the imports but this wont merge retaining any data.
06-18-2015 11:21 AM
The VARn is likely because you either had a column without any heading or it exactly duplicated another variable.
Add Guessingrows= 32000. If the first few rows of a variable had no values (blank) then SAS guessed that they were character.
I am guessing that since you wanted to set these five data sets that possibly they all had (or were supposed to have the same structure?
If so look in the log after importing one of the sheets. There will be data step code you can copy and paste into the editor and modify to change the variable names and the type. Change the INFORMAT VAR2 $1. ; to something like INFORMAT Total best12.;
Make sure that you are consistent about changing all occurrences of each variable name to the same thing. As a minimum in the INFORMAT and INPUT statement. Except for dates I generally drop the format statements as they are the default associated with the INFORMAT.
If they had the same format then you can use the same program just change the Infile statement and the dataset name.
Note about posting to the forum using Internet Explorer: copy your code to NOTEPAD and copy from there to post here. That is the most likely reason that some of the first words of your code are missing and there are extra blank lines.