topic Re: Change the order of the variable to merge two data sets in SAS Programming

Change the order of the variable to merge two data sets

GN0001 — Sun, 28 Mar 2021 19:12:15 GMT

This code doesn't work why?

PROC IMPORT OUT=Balance_Bank1
DataFile = "/home/nguityn0/Balance_Bank.xlsx"
dbms=xlsx REPLACE;
Sheet="Sheet1";
INPUT Acc_Number$ Date Debit Credit;
getnames=NO;
RUN;
Proc Sort Data=Balance_Bank1 OUT=Balance_Bank1Sorted;
BY Acc_number;
RUN;
PROC IMPORT OUT=Balance_Bank2
DataFile = "/home/nguityn0/Balance_Bank.xlsx"
dbms=xlsx REPLACE;
Sheet="Sheet2";
INPUT Acc_Number$ Date Debit Credit;
getnames=NO;
Run;
Proc Sort Data=Balance_Bank2 OUT=Balance_Bank2Sorted;
BY Acc_number;
RUN;
Data NewCollection;
MERGE Balance_Bank1Sorted Balance_Bank2Sorted;
BY ACC_Number;
RUN;

What is the problem?

Blue Sky

Re: Change the order of the variable to merge two data sets

SASKiwi — Sun, 28 Mar 2021 19:23:48 GMT

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

Re: Change the order of the variable to merge two data sets

PaigeMiller — Sun, 28 Mar 2021 20:11:27 GMT

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.

Re: Change the order of the variable to merge two data sets

ballardw — Sun, 28 Mar 2021 20:15:27 GMT

Doesn't work is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the "</>" icon to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the "</>" icon or attached as text to show exactly what you have and that we can test code against.

With that in mind, Proc Import makes guesses as to variable type and length separately for each file.

So depending on the actual values present in the two files your Acc_number could be character in one file and numeric in the other which would have a note in the log about mismatched data type.

Another possibility is that if both are character the lengths are different which could lead to values you expect to match not matching as 'A0123' is not the same as 'A01234'.

Another possibility if the variables are character is that values are entered differently. Something in one set might be entered as 'a0123' and 'A0123' in the others. So the case of the letter A doesn't match. There are quite a number of possible value mismatches in this sort of scenario, leading zeroes in one file and not in the other, all upper case, mixed case, lower case and spelling such as 'ABC Co.' 'ABC Co' 'ABC Company' and such, none of these match the others.

Re: Change the order of the variable to merge two data sets

mkeintz — Mon, 29 Mar 2021 04:15:04 GMT

@GN0001 :

As others have pointed out, simply saying your code "doesn't work", ... doesn't work.

Show the log at least, and describe what you got vs what you expected.

Re: Change the order of the variable to merge two data sets

GN0001 — Mon, 29 Mar 2021 04:40:18 GMT

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;

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

84 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;>

Regards,

Blue

Re: Change the order of the variable to merge two data sets

GN0001 — Mon, 29 Mar 2021 04:43:17 GMT

Hello PaigeMiller,

Variables are columns or fields and observations are rows.

I need to change the order of variables.

Respectfully,

Blue Sky

Re: Change the order of the variable to merge two data sets

GN0001 — Mon, 29 Mar 2021 05:02:19 GMT

Hello ballardw,
Thanks for the response. It is interesting to know these codes. The first one gave me an error and I am not able to fix it.
The second one created a data set which is different than mine.
I use merge to append the data sets and I think the order of variable are important. The data type is not important because I heard that SAS accepts mixed data types in one variable.
I posted my code and my log any way. The code doesn't arrange the variables on the order that I need for merging.
I might get a job to write SAS programming. It has been for two days that I am trying to merge to data sets and even my proc sort doesn't sort properly. I told them that I don't have any experience and I am working on it. If if is going to be this hard and challenging, I am going to let this job go.
Regards,
Blue Sky

Re: Change the order of the variable to merge two data sets

GN0001 — Mon, 29 Mar 2021 05:03:04 GMT

I posted the code and the log.

Thanks for the response.

Blue Sky

Re: Change the order of the variable to merge two data sets

SASKiwi — Mon, 29 Mar 2021 05:14:32 GMT

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?

Re: Change the order of the variable to merge two data sets

japelin — Mon, 29 Mar 2021 05:21:04 GMT

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.

Re: Change the order of the variable to merge two data sets

GN0001 — Mon, 29 Mar 2021 05:21:13 GMT

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

Re: Change the order of the variable to merge two data sets

GN0001 — Mon, 29 Mar 2021 05:23:18 GMT

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

Re: Change the order of the variable to merge two data sets

japelin — Mon, 29 Mar 2021 05:36:27 GMT

copy and paste, then submit.

It will work.

Re: Change the order of the variable to merge two data sets

SASKiwi — Mon, 29 Mar 2021 06:32:07 GMT

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

Re: Change the order of the variable to merge two data sets

PaigeMiller — Mon, 29 Mar 2021 12:43:52 GMT

@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.

Re: Change the order of the variable to merge two data sets

andreas_lds — Mon, 29 Mar 2021 13:29:47 GMT

@GN0001 wrote:

I posted the code and the log.

Thanks for the response.

Blue Sky

After so many posts it is still not clear what does not work.

proc import reads the variable in the order they have in file it processes. The procedure has no parameters to change the order of variables. Btw. afaik only one procedure needs variables in a certain order: proc export. For anything else the order of variable is hardly relevant.

Re: Change the order of the variable to merge two data sets

PaigeMiller — Mon, 29 Mar 2021 13:33:53 GMT

@GN0001 wrote:

I posted the code and the log.

Thanks for the response.

Blue Sky

The log you posted does not show errors. You need to post a log that shows the ERRORs you mentioned in your first post. That is what we need to see.

Re: Change the order of the variable to merge two data sets

ballardw — Mon, 29 Mar 2021 14:21:49 GMT

@GN0001 wrote:
Hello ballardw,
Thanks for the response. It is interesting to know these codes. The first one gave me an error and I am not able to fix it.
The second one created a data set which is different than mine.
I use merge to append the data sets and I think the order of variable are important. The data type is not important because I heard that SAS accepts mixed data types in one variable.
I posted my code and my log any way. The code doesn't arrange the variables on the order that I need for merging.
I might get a job to write SAS programming. It has been for two days that I am trying to merge to data sets and even my proc sort doesn't sort properly. I told them that I don't have any experience and I am working on it. If if is going to be this hard and challenging, I am going to let this job go.
Regards,
Blue Sky

Highlighted text is false, at least once data is in a SAS data set. One example that creates two data sets with the same named variable of different types. Note the error when combining data.

12   data one;
13      x='1';
14   run;

NOTE: The data set WORK.ONE has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds


15
16   data two;
17      x=1;
18   run;

NOTE: The data set WORK.TWO has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


19
20   data combined;
21     set one two;
ERROR: Variable x has been defined as both character and numeric.
22   run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.COMBINED may be incomplete.  When this step was stopped there were 0
         observations and 1 variables.

SAS will assign one of two variable types when reading data: character or numeric. There is no "mixed" data type.

And an example of different length variables.

24   data three;
25      x='a longer value for x';
26   run;

NOTE: The data set WORK.THREE has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds


27
28   data combined2;
29     set one three;
30   run;

WARNING: Multiple lengths were specified for the variable x by input data set(s). This can cause
         truncation of data.
NOTE: There were 1 observations read from the data set WORK.ONE.
NOTE: There were 1 observations read from the data set WORK.THREE.
NOTE: The data set WORK.COMBINED2 has 2 observations and 1 variables.

When you look at combined2 data set you will see the values of x are '1' and 'a'.

So it is critical to look at your log when combining data sets for messages like the "defined as both character and numeric" as that is an error and the "multiple lengths" can mean the your data is truncated.

Which is why showing a LOG is important when we ask for one. It is clear in the two examples what could be a couple of the "didn't work" scenarios.

Re: Change the order of the variable to merge two data sets

Tom — Mon, 29 Mar 2021 14:57:37 GMT

This code as posted cannot work. In the PROC IMPORT steps you are telling it NOT to use the first row in the spreadsheet as the names of the variables. So there cannot be a variable named Acc_number in either SAS dataset. When you tell PROC IMPORT that the source does not include names it will either name the variables A, B, C, .... or VAR1, VAR2, VAR3, ... depending on the type of DBMS you are reading from.

PROC IMPORT does not support the INPUT statement.

Are you sure your assignment is to read from an XLSX file? Perhaps you are instead supposed to be reading from text files, like comma separated values (aka CSV) files? In that case uses a data step and not proc import to read the file. Then you can have complete control over the names, types, length and even the order of the variables.