BookmarkSubscribeRSS Feed
DeepakVelusamy
Calcite | Level 5

I have a file with multiple columns out of which A has the primary index. For this primary index, I need to find customer ID and later multiple values from datasets like address, name, city, zip etc.

The problem is no dataset has all data for a particular 'A' in a single set. Can you guys help me to find all the values for each 'A' using fuzzy logic.

7 REPLIES 7
ballardw
Super User

You really aren't looking for values of A, you are looking to join up the data associated with A. And if you value A is a primary index there should be no fuzzy logic needed.

It helps to provide at least variable names and hopefully some example data.

A basic stub is something like:

proc sql;

     create combined data as

     select first.*, second.*

     from first join second on first.a=second.a;

quit;

This code will complain about variables, at least A, being duplicated. If there are duplicate values for A in either data set you will get an output record for each match: 3 values in First and 2 in Second yield 6 records.

You can use first.varname1, second.varname3 to specify the exact variables you want.

DeepakVelusamy
Calcite | Level 5

Ballardw,

Thanks for your response. Being more precise, I am not sure of what table has these data. I want run a loop to read all tables( in particular database) with has specific columns to match 'Primary Index'

ballardw
Super User

You can find all of the data sets with a specified variable using the dictionary tables

proc sql;

     select name, libname, memname

     from dictionary.columns

     where upcase(name)="YOURINDEXVARIABLENAMEGOESHERE"

   /* if you know the name of the library then add this below, the libname should be upper case */

     and libname='MYLIB'

     ;

quit;

DeepakVelusamy
Calcite | Level 5

To make the process clear.
1. Need to check all the tables in a particular database that has particular columns ( Name, Address, Zip,....... for each primary index)

2. if a table has such columns, need to map them with primary index and form a new table

3. need a automated way of checking all the folders and looping them

Note: a single table will not have all the required data. Need to combine data for multiple table to get data for particular primary index.

Reeza
Super User

Look at the dictionary tables, specifically the COLUMN table. 

ballardw
Super User

Are these tables SAS datasets? If so information does exist in dictionary.columns: this SAS built table, view actually, contains information such as name, data type, format, informat, length, label about every variable in every dataset the SAS install currently sees.

Do you know the names of the variables that make up your primary index? The example code I posted will find them using  where upcase(name) IN ("NAME","ADDRESS","ZIP")

or what ever you have named them. If you do not know the names of the variables but they have labels that are descriptive you may be able to find them looking in the label field of that dictionary table.

Reeza
Super User

That may not work depending on your data structure, just something to keep in mind.

What if you have a field that is PrimaryIndex>SecondaryIndex>Data Value

I think it's snowflake vs star schema.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1083 views
  • 3 likes
  • 3 in conversation