BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Geeman
Fluorite | Level 6

Hey SAS,

I have 3 variable;

Id  =  which is character has leading zeros ie: 0005

Name = character

Reports_to = character has leading zeros ie: 0008

My sas dataset looks like this:

IdNameReports_to_idReports_to_name

000006

Smith, John

000007

Phil, Elias

000007

Phil, Elias

000010

Santa, Clause

000008

Turner, Sandra

200000

Andrew, Skype

000010

Santa, Clause

200000

Andrew, Skype

200000

Andrew, Skype

000006

Smith, John

520001

Walker, Walker

000006

Smith, John

000006

In excel, I use a Vlookp to populate Reports_To_Name; it bassically shows the persons supervisor that he/she reports to bassed ont he reports_to_id; how would I go about doing that in SAS.....Any suggestions..

Thanks,

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

Just join the table with the variable that needs "looking up" with the table that has the decoded variable that you want to look up.

In this case you seem to be using the same table for both.

data have ;

  infile cards dsd dlm='|' truncover;

  length id reports_to_id $8 name expected $20;

  input id name reports_to_id expected ;

cards;

000006|Smith, John|000007|Phil, Elias

000007|Phil, Elias|000010|Santa, Clause

000008|Turner, Sandra|200000|Andrew, Skype

000010|Santa, Clause|200000|Andrew, Skype

200000|Andrew, Skype|000006|Smith, John

520001|Walker, Walker|000006|Smith, John

run;

proc sql ;

  create table want as

    select a.*,b.name as reports_to_name

    from have a left join have b

    on a.reports_to_id = b.id

    order by a.id

  ;

quit;

View solution in original post

15 REPLIES 15
Hima
Obsidian | Level 7

Import the excel sheet to convert it into sas data set and then join with ID's.

Geeman
Fluorite | Level 6

Thank you Hima,

The dataset is already in a SAS data set, Id like to create the reports_to_name from Name and corresponding ID.

Thank you,

ballardw
Super User

One approach would be to use your SAS data set to create a custom format and then assign to the ID as needed.

First step would be to get an ID and Name only version of the data with only one record per Id.

Create a data set from that information to make a CNTLIN dataset for use with proc format with the ID renamed to START', the Name renamed to LABEL, add FMTNAME with a text value similar to "$ID2NAME";

Then

Proc format cntlin=<thatdatasetname>;run;

IF this data is relatively static then you may want to save the format to a permanent library and add that library to the format search path

options append=(fmtserach=<yourlibrarynamehere>));

Geeman
Fluorite | Level 6

Thank you Ballardw. I'll give that I try...I did somehting similar but then i keept getting  this error "This range is repeated, or values overlap: 1-1." I'll try your method. Stay tuned. Smiley Happy

ballardw
Super User

That error usually means you have the START value, ID in your case, duplicated somewhere. If your data accidentally has two Names associated with the same ID then then that issue needs to be addressed.

Select distinct in Proc SQL may be your friend here.

Geeman
Fluorite | Level 6

Still getting the same error, I have Ids that are the same which I need, since the person is acting position for another person. Now i am not sure how to programe that...I might just use the vloockup in Excel then import the file...I would have liked to automate that process.

Hima
Obsidian | Level 7

Can you clarify if this how your data is?

Data set 1

200000 Andrew, Skype 000006

520001 Walker, Walker 000006

200000 Andrew, Skype 000006

520001 Walker, Walker 000006

000006 Smith, John 000007

000007 Phil, Elias 000010

000008 Turner, Sandra 200000

000010 Santa, Clause 200000

Data set 2

000006 Smith, John

000010 Santa, Clause

000007 Phil, Elias

200000 Andrew, Skype

Output

200000 Andrew, Skype 000006 Smith, John

520001 Walker, Walker 000006 Smith, John

200000 Andrew, Skype 000006 Smith, John

520001 Walker, Walker 000006 Smith, John

000006 Smith, John 000007 Phil, Elias

000007 Phil, Elias 000010 Santa, Clause

000008 Turner, Sandra 200000 Andrew, Skype

000010 Santa, Clause 200000 Andrew, Skype

stat_sas
Ammonite | Level 13

proc sql;

select a.*,b.name as Reports_to_name from

have a inner join have b

on a.Reports_to_id=b.id;

quit;

Geeman
Fluorite | Level 6

thanks so much! it worked!

Tom
Super User Tom
Super User

Just join the table with the variable that needs "looking up" with the table that has the decoded variable that you want to look up.

In this case you seem to be using the same table for both.

data have ;

  infile cards dsd dlm='|' truncover;

  length id reports_to_id $8 name expected $20;

  input id name reports_to_id expected ;

cards;

000006|Smith, John|000007|Phil, Elias

000007|Phil, Elias|000010|Santa, Clause

000008|Turner, Sandra|200000|Andrew, Skype

000010|Santa, Clause|200000|Andrew, Skype

200000|Andrew, Skype|000006|Smith, John

520001|Walker, Walker|000006|Smith, John

run;

proc sql ;

  create table want as

    select a.*,b.name as reports_to_name

    from have a left join have b

    on a.reports_to_id = b.id

    order by a.id

  ;

quit;

Geeman
Fluorite | Level 6

Thanks Tom! I'll give that a try ...finger crossed!! Smiley Happy

Geeman
Fluorite | Level 6

Why would I be getting more oberservations in a left join then in a inner join?

Ksharp
Super User

inner join is actually Cartesian Product .Left join include all of the obs from left dataset , suggest using Left join .

BTW, another way is using Hash Table.

Xia Keshan

nitram29
Calcite | Level 5

Could anyone walk me through the proc sql in this solution?

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 Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 15 replies
  • 29338 views
  • 6 likes
  • 8 in conversation