Appending 2 excel files

[Novice72]

I have 2 excel files (file 1 has 5 columns, file 2 has 34 columns). I am trying to append 1018 from file 2 to the bottom of file based on a value in column 34 of file 2. I do not fully understand the syntax of the PROC APPEND statement. I have reviewed many a website for a simple explanation and example of the syntax needed. I have gotten to the point and then I am stuck as to what the next statement is to be:

PROC APPEND BASE=WORK.EXCELFILE1

APPENDVER=V6

DATA=WORK.EXCELFILE2

[Tom]

PROC APPEND is probably not the right tool for what you want.  That is for stacking datasets that have the same structure.  Your description of the dataset shows they are very different.

What is it you want to do with the data.  Do not talk about column numbers.  Talk about the variables in the data.  Do not talk about row numbers, talk about observations.  To identify an specific observation explain what values of the variables can be used to find that observation.

Explain what you are trying to do.  What do the two datasets contain.  Why do they need to be combined?

[Reeza]

You likely need a SET statement. 

Selecting based on row numbers is not a good idea. Is there other filters you have?

data want;
set sashelp.class (in=t1) /*all records*/
    sashelp.class(in = t2 firstobs=12 obs=13 keep = age sex weight) /*keep certain variables and only 2 records*/
    sashelp.class (in=t3 where=(sex='M')) /*only records that are for males*/;
source = t1*1 + t2*2 + t3*3;
run;

---

@Novice72 wrote:

I have 2 excel files (file 1 has 5 columns, file 2 has 34 columns). I am trying to append 1018 from file 2 to the bottom of file based on a value in column 34 of file 2. I do not fully understand the syntax of the PROC APPEND statement. I have reviewed many a website for a simple explanation and example of the syntax needed. I have gotten to the point and then I am stuck as to what the next statement is to be:

PROC APPEND BASE=WORK.EXCELFILE1

APPENDVER=V6

DATA=WORK.EXCELFILE2

---

[ballardw]

Proc Append is intended to work with SAS data sets. It is also sensitive to the data content, meaning that all the variables have the same name, type and in the case of character variables that the lengths be the same. Otherwise results may not be as expected or at all. Before attempting to append data sets from Excel sources you want to verify that the sets are the same as described above. Proc Append will not append a set that has different variables than the Base= data set. The FORCE option will allow an append of those that do match but will not add any variable not in the Base= data set. If the variables are of different types it will not append due to errors.

You have the basic syntax given to SAS data sets. You need a ; to end the Proc statement and a  Run; to end the proc call.

PROC APPEND BASE=WORK.EXCELFILE1
APPENDVER=V6
DATA=WORK.EXCELFILE2 ;
run;

You almost certainly do not actually need the Appendver= option to behave as SAS Version 6 did appends.

The DATA step will allow stacking data sets with different variables and align those that match with requirement that like named variables be of the same type. However if you have character variables of different lengths then some work may be needed to avoid truncated data .

Data want;   
   set work.Excelfile1
        work.excelfile2
  ;
run;

But again this expects SAS data sets.

You do not mention any details of the "based on a value in column 34". Which is not an append operation at all. Append is stack vertically unconditionally.

You would have to provide examples of BOTH source data sets and all the rules involved on the "value in column 34".

[Novice72]

Hello,

It doesn't matter anymore. The person I was done this for has decided to go another way and use Power BI instead. Thank you for your advice though. Appreciate it. 

[AMSAS]

@Novice72 Can you provide more information on what you are attempting to do
If you could provide sample input data and what you expect to be in your output data then we can help you further
For Example:

data work.have1 ;
	infile cards ;
	input
		var1 : $20.
		var2 : $20. ;
	output 
		work.have1 ;
cards ;
Ob1_Variable1 Ob1_Variable2
Ob2_Variable1 Ob2_Variable2
Ob3_Variable1 Ob3_Variable2
;
run ;

data have2 ;
	infile cards ;
	input
		var2 : $20.
		var3 : $20. ;
	output 
		work.have2 ;
cards ;
Ob1_Variable2 Ob1_Variable3
Ob2_Variable2 Ob2_Variable3
Ob3_Variable2 Ob3_Variable3
Ob4_Variable2 Ob3_Variable3
Ob5_Variable2 Ob3_Variable3
;
run ;

data want ;
	infile cards ;
	input
		var1 : $20.
		var2 : $20.
		var3 : $20. ;
	output 
		work.want ;
cards ;
Ob1_Variable1 Ob1_Variable2 Ob1_Variable3
Ob2_Variable1 Ob2_Variable2 Ob2_Variable3
Ob3_Variable1 Ob3_Variable2 Ob3_Variable3
. Ob4_Variable2 Ob3_Variable3
. Ob5_Variable2 Ob3_Variable3
;
run ;

[Novice72]

Hello,

It doesn't matter anymore. The person I was done this for has decided to go another way and use Power BI instead. thank you for your advice though. Appreciate it.