topic Re: compare variables in SAS Programming

compare variables

Emma8 — Thu, 09 Jul 2020 14:08:36 GMT

I have a dataset with about 1000 variables. There are many variables that are coded the same but names are different, see an example, those variables have the same information: nHits and fewfe, also those variables have the same values except League contain some missing data: League dfgwe_23.

How can I find out those variables? So, I wanted to get the list of variables (see below attached) with the same values. In this example fake data, the following variables have the same values except some missing (should ignore missing). Thank you.

Want the following list:

CrAtBat = ab
CrBB =fkae
CrHits =vrgsd
CrHome =dfwef
CrRbi =vewef
CrRuns =fff
Div =wsfde_d
Division = vdfgew
League =dfgwe_23
Position = fgerg
Salary =vregf
Team =fre
YrMajor = rewa
logSalary = fwef
nAssts =cefwe
nAtBat =efqwef
nBB =qfqewfq
nError =qfefe
nHits =fewfe

Re: compare variables

ketpt42 — Thu, 09 Jul 2020 13:13:43 GMT

Did you attach the correct file? It doesn't match the description you gave.

Re: compare variables

andreas_lds — Thu, 09 Jul 2020 13:15:32 GMT

What do you mean by "coded the same" exactly?

i am not that fond of writing a data step to read a file with 1k variables, please share the code you have used to read the file.

Re: compare variables

Kurt_Bremser — Thu, 09 Jul 2020 13:20:55 GMT

That looks like a bad joke. There are several columns with identical names, there's no logical order, the header line can't be used for valid SAS names, .....

If it had been something like

player_name position_1986 salary_1986 at_bats_1986 position_1987 salary_1987 at_bats_1987

and so on, then you could rather easily transpose it into something useful, but this?

"Player's Name","Team at the End of 1986","Times at Bat in 1986","Hits in 1986","Home Runs in 1986","Runs in 1986","RBIs in 1986","Walks in 1986","Years in the Major Leagues","Career Times at Bat","Career Hits","Career Home Runs","Career Runs","Career RBIs","Career Walks","League at the End of 1986","Division at the End of 1986","Position(s) in 1986","Put Outs in 1986","Assists in 1986","Errors in 1986","1987 Salary in $ Thousands","League and Division","Log Salary","Team at the End of 1986","Times at Bat in 1986","Hits in 1986","Walks in 1986","Years in the Major Leagues","Career Times at Bat","Career Hits","Career Home Runs","Career Runs","Career RBIs","Career Walks","League at the End of 1986","Division at the End of 1986","Position(s) in 1986","Assists in 1986","Errors in 1986","1987 Salary in $ Thousands","League and Division","Log Salary"

Re: compare variables

Emma8 — Thu, 09 Jul 2020 13:44:05 GMT

Sorry, just attached a correct file.

Re: compare variables

Emma8 — Thu, 09 Jul 2020 14:00:35 GMT

Sorry, I updated the file again

Re: compare variables

Kurt_Bremser — Thu, 09 Jul 2020 14:10:36 GMT

To assist you in finding possible duplicate columns, you can do this:

proc import
  out=baseball
  datafile="/folders/myfolders/baseball2.csv"
  dbms=csv
  replace
;
run;

proc transpose data=baseball out=long;
by name;
var _numeric_;
run;

proc sort data=long;
by name col1;
run;

Re: compare variables

Shmuel — Thu, 09 Jul 2020 14:15:20 GMT

1) Are those duplicates all only numeric type or also character type?

2) Can it be that for some observations their values are the same but in other observation,

those same variables have different value?

3) Assume you found the duplicate values, what do you want to do with that information?

What kind of output you want?

Mainly, theoretically, you can define an array, sort it and then compare values easily.

Re: compare variables

Emma8 — Thu, 09 Jul 2020 14:57:00 GMT

Thank you.
Numeric variables should compare with numeric and character variables should compare with character—-that is the goal =finding variables with the same values.
I do not want any output data. I want just the list of variables with the same values.
For example, if variable1’s values are equal with variable2’s values, then I want
Variable1 and variable2, etc

Re: compare variables

Emma8 — Thu, 09 Jul 2020 15:02:45 GMT

This output I would like to :

CrAtBat = ab
CrBB =fkae
CrHits =vrgsd
CrHome =dfwef
CrRbi =vewef
CrRuns =fff
Div =wsfde_d
Division = vdfgew
League =dfgwe_23
Position = fgerg
Salary =vregf
Team =fre
YrMajor = rewa
logSalary = fwef
nAssts =cefwe
nAtBat =efqwef
nBB =qfqewfq
nError =qfefe
nHits =fewfe

Re: compare variables

Emma8 — Thu, 09 Jul 2020 15:24:40 GMT

There are no duplicates

Re: compare variables

Shmuel — Thu, 09 Jul 2020 15:24:43 GMT

You have not answer the second question:

2) Can it be that for some observations their values are the same but in other observation,

those same variables have different value? (neglecting missing values).

See solution in next link - a very similar question:

https://communities.sas.com/t5/SAS-Programming/Values-the-same/m-p/663839/highlight/false#M198244

Re: compare variables

Emma8 — Thu, 09 Jul 2020 17:07:49 GMT

Let me go back.
I have 2 datasets.
Dataset 1 has 20000 observations and 300 variables.

Dataset 2 has 10000 observations and 500 variables.

I want to compare those datasets and list out all variables with the same values (one may have more ID (names as suggests the dataset observations) and missing values. I should ignore those additional observations or missing, just compare existing ids variables in both datasets

Re: compare variables

Kurt_Bremser — Thu, 09 Jul 2020 17:34:57 GMT

That's completely different from your initial question. See Maxim 42.

Re: compare variables

Shmuel — Thu, 09 Jul 2020 18:15:40 GMT

What do you mean by "one may have more ID" - is ID unique at one or both datasets?

Suppose you have unique ID in table1 bot more than one observations of same id in table 2 - what do you want to compare? and how to treat the many to one matching ?

I suggest post a sample of each table with upto 10 observations and choose a sample of variables you noticed as same vale and few of different value to serve as test date, then post the output you expect regarding those samples.

Re: compare variables

Emma8 — Thu, 09 Jul 2020 18:24:50 GMT

In one dataset, there are variables before “=“ and in another dataset, there are variables after “=“ sign:
CrAtBat = ab
CrBB =fkae
CrHits =vrgsd
CrHome =dfwef
CrRbi =vewef
CrRuns =fff
Div =wsfde_d
Division = vdfgew
League =dfgwe_23
Position = fgerg
Salary =vregf
Team =fre
YrMajor = rewa
logSalary = fwef
nAssts =cefwe
nAtBat =efqwef
nBB =qfqewfq
nError =qfefe
nHits =fewfe

Re: compare variables

Shmuel — Thu, 09 Jul 2020 19:32:58 GMT

Your post

In one dataset, there are variables before “=“ and in another dataset, there are variables after “=“ sign:
CrAtBat = ab
CrBB =fkae
CrHits =vrgsd
........ etc. ....

does not answer my question - "

What do you mean by "one may have more ID" - is ID unique at one or both datasets?

Suppose you have unique ID in table1 but more than one observations of same id in table 2 - what do you want to compare? and how to treat the many to one matching ?"

Example 1

suppose table_1 contains:

ID VAR_A

22 333

and table_2 contains:

ID VAR_X

22 340

22 500

22 333

22 999

What output you want? is any other variable or attribute in table_2 that distinguish between

the observations of same ID? - such as serial number or date or ...

in order to report:

var_A = var_X when ID=22 and Table_2 date=...

Example 2:

suppose table_1 contains:

ID VAR_A

22 333

25 444

and table_2 contains:

ID VAR_X

22 333

25 777

Would it be correct to say that:

var_A = var_X ???

Re: compare variables

Emma8 — Thu, 09 Jul 2020 19:17:22 GMT

Name from both datasets are IDs.
Var_x is different than var_a, obviously.
In one dataset, there are additional observations or missing —those cases should be ignored.

Re: compare variables

Shmuel — Thu, 09 Jul 2020 19:48:58 GMT

I can accept asking to ignore cases of non equal values, either null or not,

but: Saying "Var_x is different than var_a, obviously." - why?

in Example_1 there are ID=22 with VAR_A=VAR_X=333

in Example_2 there are ID=22 with VAR_A=VAR_X=333 (same as in example 1)

Are you asking to report variable names only if there is at least one pair of observations

with equal values on all IDs ?

Re: compare variables

Emma8 — Thu, 09 Jul 2020 20:37:42 GMT

So, I made 2 datasets:
I want to get just list of variables with the same values except missing:
For example, bothe datasets have the same Id-s (variable "name"),
CrAtBat = ab
CrBB =fkae
CrHits =vrgsd
CrHome =dfwef
CrRbi =vewef
CrRuns =fff
Div =wsfde_d
Division = vdfgew
League =dfgwe_23
Position = fgerg
Salary =vregf
Team =fre
YrMajor = rewa
logSalary = fwef
nAssts =cefwe
nAtBat =efqwef
nBB =qfqewfq
nError =qfefe
nHits =fewfe