DATA Step, Macro, Functions and more

How do you convert an excel data file to sas data set: SAS 9.4

Reply
Occasional Contributor
Posts: 10

How do you convert an excel data file to sas data set: SAS 9.4

I need help in converting the attached excel data file to a sas data set. The attached excel file is a truncated .xls file as there are file size limitations for an attachment.

 

I am also attaching the sas program which i wrote but there are errors I am encountering. 

 

would be grateful for help for pointing out the errors in the program given below.

 

best regards and thanks

 

Raman

 

PS: bas sas program code is given below. I had saved the excel file into a text file prior to submitting the below code

 

libname TEST1 'F:test ';
LRECL = 150;
data TEST1.K1_Out;
infile 'F:test\Kud_Data1.txt';


input
@1 SC 1 @9 TKTID 6 @17 CUSTID $ 8. @25 ProdID $ 10 @25 ProdID $ 10 @41 EMPID 6
@49 QTY 1 @59 Tax:5.2 @65 R_prix 7.2
@73 D_A 7.2 @81 D_Per 5.2 @89 T_Amt 8.2@89 T_Amt 8.2
@97 St_Time & $ 22 @122 B_Type $ 1
@130 M_Disc 4
@138 Oth_Disc 7.2
@147 Sal_St $ 1;

 

run;

Super User
Posts: 11,343

Re: How do you convert an excel data file to sas data set: SAS 9.4

Posted in reply to r_sethi2001

How I convert often involves screaming and kicking.

 

First the code you are showing looks like reading a text file with fixed columns. Excel files are not text and really don't have any concept of fixed column width.

 

Generally I convert Excel files to CSV (File Save AS from Excel) and then use a data step to read the file. Often I use the import wizard, or Proc Import, for CSV setting a LARGE value for guessing rows. One advantage of this approach is that data step code to read the file is generated and can be saved and modified to be a "nicer" program by adding labels, use of custom informats/formats, changing variable names( things from Excel seem to often have names like CLIENT_PERSONAL_0_VOLUNTARILY_IN which I might want to shorten) to make more sense from column headings. Then if I have another of these files to read later I do the conversion to CSV, point the program to read the new file and write to a different SAS data set. Another advantage of this approach is I know what type is variable is and the characteristics. Reading directly from Excel often yeilds changes in columns from text to numeric, lengths of text variables and sometimes even the variable names.

 

Occasional Contributor
Posts: 10

Re: How do you convert an excel data file to sas data set: SAS 9.4

thanks for the approach I never thought of the CSV file. I chose the comma delimited option
what should i do about the rec length. There are 147 char in my source data rec. The log file of the data step states the rec length in 99 chars.
can i use the LRECL system option in bas sas. I think this needs to be set in the config file 
look forward to your reply, in the mean time i going down the path you suggested
best regards raman
Super User
Posts: 11,343

Re: How do you convert an excel data file to sas data set: SAS 9.4

Posted in reply to r_sethi2001

You may not have set a large enough value for GUESSINGROWS if you used the wizard or Proc Import. By default onlyt the first 20 rows are "examined" to set properties of the file.

 

You could either go through the proc import or wizard and increase that value OR copy the code from the Log and modify it to have a larger LRECL parameter. Or set the informats for the character variables to your expectations.

Occasional Contributor
Posts: 10

Re: How do you convert an excel data file to sas data set: SAS 9.4

i have made progress in converting my excel data file. The data is now being read correctly. The code is working fine, the log file is reproduced below.
QUESTION : my source file has 138000 observation out of which only 65535 were read. I have set obs = 150000
would be very grateful for your help
best regards and thanks
raman

libname TEST1 'F:test';           filename indata1 'F:test\KDATA100.csv';
data TEST1.K11_Out;length St_Time $ 22;infile indata1 dsd dlm=',' obs= 150000 lrecl=120 truncover;input   SC $ TKTID CUSTID $  ProdID: $10. EmpIDQTY   Tax:5.2  R_Prix:7.2  D_A:7.2 D_P:5.2 T_Amt:8.2  St_Time $ B_type $M_Disc    O_disc:7.2  S_Sta $ ;

run;

226            libname TEST1 'F:test';NOTE: Libref TEST1 refers to the same physical library as TMP7.NOTE: Libref TEST1 was successfully assigned as follows:      Engine:        V9      Physical Name: F:\test227            filename indata1 'F:test\KDATA100.csv';228229  data TEST1.K11_Out;230  length St_Time $ 22;231  infile indata1 dsd dlm=',' obs= 150000 lrecl=120 truncover;232  input   SC $ TKTID CUSTID $  ProdID: $10. EmpID233  QTY   Tax:5.2  R_Prix:7.2  D_A:7.2 D_P:5.2234   T_Amt:8.2235    St_Time $ B_type $236  M_Disc    O_disc:7.2  S_Sta $ ;237238239240241  run;
NOTE: The infile INDATA1 is:      Filename=F:\test\KDATA100.csv,      RECFM=V,LRECL=120,File Size (bytes)=6548068,      Last Modified=15Sep2016:07:19:31,      Create Time=15Sep2016:07:13:18
NOTE: 65535 records were read from the infile INDATA1.      The minimum record length was 84.      The maximum record length was 112.NOTE: The data set TEST1.K11_OUT has 65535 observations and 16 variables.NOTE: DATA statement used (Total process time):      real time           0.19 seconds      cpu time            0.17 seconds
Super User
Super User
Posts: 7,054

Re: How do you convert an excel data file to sas data set: SAS 9.4

Posted in reply to r_sethi2001

Your sheet looks well formed.

Did you try just letting SAS convert it for you?

libname myxls xls 'path to xls file';
proc copy inlib=myxls outlib=work;
run;
Ask a Question
Discussion stats
  • 5 replies
  • 1006 views
  • 0 likes
  • 3 in conversation