01-24-2013 06:52 AM
One Sas Data set is already present in the SAS. I want to insert record into this Sas Data set from an external .xls file.
I don't want to replace the existing Data Set and don't want to recreate the structure of the Data Set, Just want to delete the existing record in Data Set and insert new records from Excel sheet(.xls file).
Please give your suggestions.
01-24-2013 07:35 AM
You can always use Proc sql to delete the row then insert the row once you read it in from the excel file. It has been a while since I used insert but if memory serves you have to make sure that the variable types and lengths between the destination file and the source file are the same.
Hope this helps
01-24-2013 11:00 AM
Create a record ID.
In a datastep -> line_id+1;
in a new datastep delete the specific lines/IDs
You may still have record type issues if you are reading the excel file through a proc import statement.
You might be better off reading it in a datastep (save the excel file as a csv) with an informat statement. An easy way to make sure it's the same is to run a proc contents on the dataset you already have and output it through an ods statement to an excel file. You can then cut and paste back into your sas code.
ods html file='c:\master_file_content.xls';
proc contents data=masterfile; run;
ods html close;
infile 'c:\newfile' dlm=',' firstobs=2;
informat var1 $20. var2 comma15.2;
input var1 var2;
01-24-2013 01:18 PM
From your description it sounds like you do want to replace the old file. Using SAS datasets there is not really much down side to just do a normal data step to recreate the file. Unless you are talking about files that are really large. And it would probably be impossible to get such a large file delivered in Excel format. So you probably want to do these steps:
1) Convert Excel file to a SAS dataset using PROC IMPORT or a libref with the excel engine.
proc import datafile='newfile.xls' out=newfile ; run;
2) Recreate the file by first defining the variable names, order , etc by setting the old data with OBS=0 option.
data myfile ;
set myfile (obs=0) newfile;
Of course SAS has a nasty habit of assigning permenently attached formats and informats when creating dataset from excel (or other external databases for that matter). You could fix that by removing them before appending the dataset. Then the variables will retain the format and informat associated with them in the original SAS dataset.
set myfile(obs=0) newfile;
01-25-2013 08:22 AM
Thanks for your response.
My requirement is like I don't want to recreate the exsisting Data Set(Let say Lib.ExistData).
I just want to delete record from this sas data set(Lib.ExistData) and want to insert new records into this data set which is imported from Excel data(Let say Work.Temp).
Currently I am importing Excel data into a temp data set(Work.Temp) & inserting this data into sas data set(Lib.ExistData), but I am getting error like "Value 1 on the SELECT clause does not match the data type of the corresponding column listed after the INSERT table name". using below statement:
'Proc Sql; insert into Lib.ExistData Select * from Work.Temp ;
So, Is there any way to keep data type same in Work.Temp and Lib.ExistData while importing the data.
01-25-2013 06:20 PM
This is what I meant by Excel being a poor format for retrieving your data. SAS (or frequently Microsofts Jetengine utility) has to try to guess as to what type of variable the columns in your spreadsheet contain. And the decisions is strongly influenced by the values that happen to be in the sample at hand. So it could look at column A in today's data and decide it is numeric. Then when tomorrow's data comes in the set of values might make it look like it should be character.
If you can get the Excel sheet converted to CSV file then it would be easier. See if this does what you want with your data.
if 0 then set lib.existdata ;
infile "MyExcel.csv" dsd truncover firstobs=2;
input _all_ ;
Otherwise you are going to have to start looking at the variable definitions and generating code to convert the fields that PROC IMPORT converted to the wrong type . You can get the variable names, type, length etc into datasets by using PROC CONTENTS or querying DICTIONARY.COLUMNS.
01-28-2013 01:30 PM
When you say you want to avoid re-creating the master file, I assume that you mean you want to avoid going back to the original (non-SAS) dataset -- I know of no way to avoid creating a new SAS file if you want to delete or add observations.
I agree with Tom -- PROC IMPORT produces somewhat dodgy results. Another route to go to create the transaction file, if you don't want to use SQL, is the direct data exchange. An example of this code is:
filename DY1 dde 'Excel|[direct.xls]Test!R2C1:R6C5';
infile DY1 notab dlm='09'x dsd lrecl=400 missover pad;
length varA $10 varB $5 varC 8 /* etc. */;
informat varC yyddmm8. /* for example */;
format varC mmddyy10. /* again, for example */;
Here, the Excel file must be open when you execute the data step, and SAS reads the data as they appear formatted in the Excel file.
Then, presuming that the master file and transaction file contain some key (say, varA), you would merge the 2 files:
merge master transaction;
PROC UPDATE does not destroy the master record; it just overlays fields in the master record with non-missing values in the transaction record, and that may not be what you want to do.