BookmarkSubscribeRSS Feed
Cyclist
Calcite | Level 5

1) This ist the description of the current situation:

The input data sources for the analysis is:

  • Contract_Config file (contains info about access lines)
  • Client (contains company names and company addresses)
  • AdressMaster - contains the addresses and serves as a reference for the correct address data
  • H_File: file from the external data provider; serves as a reference for the correct company names and addresses

The goal is to:

  • connect Contract_Config with AdressMaster in order to find the correct adressess of the access lines
    • Available Fileds for connection in Contract_Config: City, Door, Housenumber, Street, ZIP_Code
    • Available Fileds for connection in AdressMaster: City_Name, Door_Number, Building_Number, Street_Name, ZIP_Code
  • connect Client and H_File to find the correct comapany names and company addresses
    • Available Fileds for connection in Client (company Names): Client_Company, Client_Company2, Client_Firstname, Client_Lastname
    • Available Fileds for connection in H_File (Company Names): Firstname, Lastname, Comapny_Name
    • Available Fileds for connection in Client (company addresses): Client_Street, Client_ZIP_Code, Client_City
    • Available Fileds for connection in H_File (Company adresses): federal_state, door_number, place, ZIP, street, street_number
  • Connect AdressMaster and H_File to connect the addresses

 

2) Since I am new to the software, I find it hard. In fact, I have already spent few days to understand this software. Based on the information above, how can I make the corresponding data jobs? Please send me more detailed information and list the necessary job items/building blocks.

 

3) Additional dfficulty is to find very good tutorials that describe in easy (not in dull and tedious way) the purpose and the correct usae of the individual building blocks in the data job. Are you familiar maybe with any such tutorial?

1 REPLY 1
Hentiedp
Fluorite | Level 6

 

Hi,

 

I think what you are looking to do (rather how I understand it) is to link the different datasets to have a combined dataset.

 

Easiest way that I can think of is to use PROC SQL for this as there seems to be couple of fields with different names that needs to be joined. If you are new to SQL, please do some reading on the topic because the different joins will produce different results. (This paper should help http://www.lexjansen.com/nesug/nesug09/bb/BB03.pdf)

 

If for example you want to join/link Client and H_file information and you assume that Client has all the information you could do the following.

proc sql;
create table client_HComb as
select c.Client_Company, c.Client_Company2, c.Client_Firstname, c.Client_Lastname,
h.Firstname, h.Lastname, h.Comapny_Name
from client c left outer join h_file h on
c.Client_Firstname=h.firstname and c.Client_Lastname=h.Lastname and c.Client_Company = a.Comapny_Name
;
quit;

 

With the left join, if no matches from the H_file (right table) is found the fields from H_file will be missing. 

 

Similar results can be accomplished with Datastep merge but there's a couple of extract hoops that you'll need to jump through.

 

But important read up on SQL before hand. 

 

Best site I can suggest to find tutorials are www.lexjansen.com. Most of the papers presented at conferences are there and if covers from basics to advance. That is my one of my first stops when I need to find something.

 

HTH

 

KR

H

 

 

 

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!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 1 reply
  • 750 views
  • 0 likes
  • 2 in conversation