Change the order of the variable to merge two data sets

[GN0001]

This code doesn't work why?

1. PROC IMPORT OUT=Balance_Bank1
2. DataFile = "/home/nguityn0/Balance_Bank.xlsx"
3. dbms=xlsx REPLACE;
4. Sheet="Sheet1";
5. INPUT Acc_Number$ Date Debit Credit;
6. getnames=NO;
7. RUN;
8. Proc Sort Data=Balance_Bank1 OUT=Balance_Bank1Sorted;
9. BY Acc_number;
10. RUN;
11.  
12.  
13. PROC IMPORT OUT=Balance_Bank2
14. DataFile = "/home/nguityn0/Balance_Bank.xlsx"
15. dbms=xlsx REPLACE;
16. Sheet="Sheet2";
17. INPUT Acc_Number$ Date Debit Credit;
18. getnames=NO;
19. Run;
20.  
21. Proc Sort Data=Balance_Bank2 OUT=Balance_Bank2Sorted;
22. BY Acc_number;
23. RUN;
24.  
25. Data NewCollection;
26. MERGE Balance_Bank1Sorted Balance_Bank2Sorted;
27. BY ACC_Number;
28. RUN;

What is the problem?

Blue Sky

[SASKiwi]

Please post your SAS log including any notes. Without this evidence we don't know what your problem is. 

[GN0001]

This is the code:

PROC IMPORT OUT=Balance_Bank1
DataFile = "/home/nguityn0/Balance_Bank.xlsx"
dbms=xlsx REPLACE;
Sheet="Sheet1";
getnames=NO;
RUN;
DAtA  Balance_Bank3;
Format Acc_Number Date Debit Credit;
SET balance_bank1;
RUN;

This is the log:

</

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

72

73 PROC IMPORT OUT=Balance_Bank1

74 DataFile = "/home/nguityn0/Balance_Bank.xlsx"

75 dbms=xlsx REPLACE;

76 Sheet="Sheet1";

77 getnames=NO;

78 RUN;

NOTE: One or more variables were converted because the data type is not supported by the V9 engine. For more details, run with

options MSGLEVEL=I.

NOTE: The import data set has 5 observations and 4 variables.

NOTE: WORK.BALANCE_BANK1 data set was successfully created.

NOTE: PROCEDURE IMPORT used (Total process time):

real time 0.00 seconds

user cpu time 0.01 seconds

system cpu time 0.00 seconds

memory 2809.84k

OS Memory 33192.00k

Timestamp 03/29/2021 04:16:28 AM

Step Count 243 Switch Count 4

Page Faults 0

Page Reclaims 667

Page Swaps 0

Voluntary Context Switches 25

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 264

79 DAtA Balance_Bank3;

80 Format Acc_Number Date Debit Credit;

81 SET balance_bank1;

82 RUN;

NOTE: Variable Acc_Number is uninitialized.

NOTE: Variable Date is uninitialized.

NOTE: Variable Debit is uninitialized.

NOTE: Variable Credit is uninitialized.

NOTE: There were 5 observations read from the data set WORK.BALANCE_BANK1.

NOTE: The data set WORK.BALANCE_BANK3 has 5 observations and 4 variables.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

user cpu time 0.00 seconds

system cpu time 0.00 seconds

memory 1168.43k

OS Memory 31660.00k

Timestamp 03/29/2021 04:16:28 AM

Step Count 244 Switch Count 2

Page Faults 0

Page Reclaims 130

Page Swaps 0

Voluntary Context Switches 10

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 264

83

84 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;>

Regards,

Blue

[SASKiwi]

You need to examine the data in your dataset WORK.BALANCE_BANK1. It contains 4 variables and 5 observations. Has it been populated as you expect? The notes in the following DATA step suggest you aren't getting the expected variable names and that may have something to do with the GETNAMES = NO statement. What happens if you try GETNAMES = YES?

[GN0001]

I changed the GETName = Yes. It doesn't give me the order that I want.

Yes, I have four varialbes: Acc_Number Date Debit Credit and 5 observations.

Regards,

Blue Skey

[SASKiwi]

Well your program doesn't include PROC SORT if you are referring to row order.

[GN0001]

Your response helped me a lot.

Thanks for it.

Respectfully,

Guity

[japelin]

The program is different from the first post. I thought the merging process didn't work properly?

Also, what are the variable names in Balance_Bank1 after import?

I'm guessing that the variables such as Acc_Number are probably not assigned correctly and thus cannot be merged.

proc contents data=Balance_Bank1 varnum;
run;

Run this and show us the list of variables.

[GN0001]

I have not learned the code you put here. I am a new learner and I need to stick to the codes that the instructor is teaching us.

Regards,

Blue Sky

[japelin]

copy and paste, then submit.

It will work.

[GN0001]

I ran your codes and this is what I got:

Variable Type Len Format Label
1 Date Num 8 MMDDYY10. Date
2 Debit Num 8 BEST. Debit
3 Credit Num 8 BEST. Credit
4 Acc_Number Num 8 BEST. Acc_Number

Respectfully,

Blue Sky

[PaigeMiller]

I assume your title should say "Change the order of OBSERVATIONS", not "change the order of variables", as the order of variables would not affect a merge.

In any event, I think your two PROC IMPORTs wind up causing errors and don't create data sets. But as stated by @SASKiwi , show us the LOG of this entire section of code, all of the LOG, that's 100%, every single character in the LOG for this part of the code, with nothing chopped out, and without you selecting parts to show us and not showing us other parts.

[GN0001]

Hello PaigeMiller,

Variables are columns or fields and observations are rows.

I need to change the order of variables.

Respectfully,

Blue Sky

[PaigeMiller]

---

@GN0001 wrote:

Hello PaigeMiller,

Variables are columns or fields and observations are rows.

I need to change the order of variables.

Respectfully,

Blue Sky

---

So you say you get errors, but you haven't shown us the errors in the LOG you provided.

You don't need to change the order of variables to achieve a merge. None of what you said or showed us makes sense.

[GN0001]

Hello,

Are you saying observation orders are not important? Can I merge two data sets although the order of variables are different?

Respectfully,

Blue sky