topic Re: How do you expand hierarchal data into a simple lookup in SAS EG? in SAS Enterprise Guide

How do you expand hierarchal data into a simple lookup in SAS EG?

SASAlex101 — Mon, 14 Feb 2022 18:22:53 GMT

I have the following Dataset:

data WORK.have;
Length EmpName $100. ManagerName $100.;
Infile datalines delimiter='#';
input EmpID ManagerID EmpName $ ManagerName $;
datalines;
1#4#WALTER WHITE#GUS FRING
2#1#JESSE PINKMAN#WALTER WHITE
3#1#SAUL GOODNAM#WALTER WHITE
4#5#GUS FRING#ELADIO VUENTE
5#5#ELADIO VUENTE#ELADIO VUENTE
6#2#SKINNY PETE#JESSE PINKMAN
7#2#COMBO#JESSE PINKMAN
;
run;

Is there a quick macro script that will find all children (if any) from the table based on EmpID / ManagerID hierarchy and produce the following output?:

data WORK.Want;
Length EmpName $100. ManagerName $100.;
Infile datalines delimiter='#';
input EmpName $ ManagerName $ Level;
datalines;
WALTER WHITE#WALTER WHITE#1
JESSE PINKMAN#WALTER WHITE#2
SKINNY PETE#WALTER WHITE#3
COMBO#WALTER WHITE#3
SAUL GOODNAM#WALTER WHITE#2
JESSE PINKMAN#JESSE PINKMAN#1
SKINNY PETE#JESSE PINKMAN#2
COMBO#JESSE PINKMAN#2
SAUL GOODNAM#SAUL GOODNAM#1
GUS FRING#GUS FRING#1
WALTER WHITE#GUS FRING#2
JESSE PINKMAN#GUS FRING#3
SKINNY PETE#GUS FRING#4
COMBO#GUS FRING#4
SAUL GOODNAM#GUS FRING#3
ELADIO VUENTE#ELADIO VUENTE#1
GUS FRING#ELADIO VUENTE#2
WALTER WHITE#ELADIO VUENTE#3
JESSE PINKMAN#ELADIO VUENTE#4
SKINNY PETE#ELADIO VUENTE#5
COMBO#ELADIO VUENTE#5
SAUL GOODNAM#ELADIO VUENTE#4
SKINNY PETE#SKINNY PETE#1
COMBO#COMBO#1

;
run;

Essentially expanding it out to a simple lookup table. I'm somewhat new to macros do I'm not too sure where to begin.

Thanks;

Re: How do you expand hierarchal data into a simple lookup in SAS EG?

Reeza — Mon, 14 Feb 2022 19:30:10 GMT

What is the rationale behind the macro requirement? This is usually a hash solution, not macros so curious as to the logic behind that requirement.

Re: How do you expand hierarchal data into a simple lookup in SAS EG?

SASAlex101 — Tue, 15 Feb 2022 06:15:51 GMT

I would be in favor of seeing the "Hash" solution. I mentioned macros because I am ignorantly assuming there isnt't any other clever way to solve the problem in SaS.

Re: How do you expand hierarchal data into a simple lookup in SAS EG?

AlanC — Tue, 15 Feb 2022 06:32:44 GMT

Macros are merely a way to generate regular SAS code. They arent anything different or special. I would suggest using a RETAIN statement.