Excel Import

Reply
New Contributor
Posts: 2

Excel Import

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

10Na_delta_pre_0Char1$1.$1.
11Na_delta_pre_max0to1Char1$1.$1.
13Na_peak_day0_1Char1$1.$1.
1OLT_IDNum8BEST12.BEST32.
5OLT_ID_Na_minNum8BEST12.BEST32.
2VAR2Char1$1.$1.
6VAR6Char1$1.$1.
12VAR12Char1$1.$1.

Liver "\\psf\Home\Desktop\Liver Rascal\CSV_Sheets";




IMPORT OUT= Liver.Sheet_1


DATAFILE= "\\psf\Home\Desktop\Liver Rascal\CSV_Sheets/Sheet_1.csv"


DBMS=CSV REPLACE;


GETNAMES=YES;


DATAROW=2; RUN;



IMPORT OUT= Liver.Dead_Sheet2


DATAFILE= "\\psf\Home\Desktop\Liver Rascal\CSV_Sheets\Dead_Sheet2.csv"


DBMS=CSV REPLACE;


GETNAMES=YES;


DATAROW=2; RUN;



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;


=YES;


DATAROW=2;


run;



IMPORT OUT= Liver.tenin24h_Sheet_4


DATAFILE= "\\psf\Home\Desktop\Liver Rascal\CSV_Sheets\tenin24h_Sheet_4.csv"


DBMS=CSV REPLACE;


GETNAMES=YES;


DATAROW=2; RUN;



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"


DBMS=CSV REPLACE;


GETNAMES=YES;


DATAROW=2; RUN;



sort data= Liver.Sheet_1;


OLT_ID;


;



sort data= LIVER.Dead_Sheet2;


OLT_ID;run;



sort data= Liver.Sheet_3;


OLT_ID;run;



sort data= Liver.tenin24h_Sheet_4;


OLT_ID;run;



sort data= Liver.MRN_Na_10_in_24h_Sheet_5;


OLT_ID;run;




Liver.all;


Liver.Sheet_1 LIVER.Dead_Sheet2 Liver.Sheet_3 Liver.tenin24h_Sheet_4 LIVER.MRN_Na_10_in_24h_Sheet_5;


OLT_ID;run;


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.

Any pointers?

ChristosK


Super User
Posts: 11,107

Re: Excel Import

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.

Ask a Question
Discussion stats
  • 1 reply
  • 234 views
  • 0 likes
  • 2 in conversation