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:
Id | Name | Reports_to_id | Reports_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,
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;
Import the excel sheet to convert it into sas data set and then join with ID's.
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,
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>));
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.
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.
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.
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
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;
thanks so much! it worked!
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;
Thanks Tom! I'll give that a try ...finger crossed!!
Why would I be getting more oberservations in a left join then in a inner join?
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
Could anyone walk me through the proc sql in this solution?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.