DATA Step, Macro, Functions and more

BASE SAS

Accepted Solution Solved
Reply
Contributor
Posts: 63
Accepted Solution

BASE SAS

Hi below is the interview question which I faced,we can solve it by hardcoding ,can we solve it without hard coding. pls help me:

 

An exposure is linked to a limit. And a limit can have multiple exposures associated to it. Similarly a limit may have multiple collateral associated to it. Collateral can be shared between multiple limits. Please see the below sample input dataset,

 

ExposureId

LimitId

CollateralId

1

L1

C1

2

L1

C2

3

L2

C1

4

L2

C3

5

L3

C4

6

L3

C5

7

L4

C6

8

L5

C5

 

You are given the task to write code and generate below output, to cluster the related exposure, limit and collateral together with a ClusterId.

 

ExposureId

LimitId

CollateralId

ClusterId

1

L1

C1

1

2

L1

C2

1

3

L2

C1

1

4

L2

C3

1

5

L3

C4

2

6

L3

C5

2

7

L4

C6

3

8

L5

C5

2


Accepted Solutions
Solution
4 weeks ago
Super User
Posts: 9,681

Re: BASE SAS

NO. I am not going to talk about Hash Table here. It is a very big topic.

If you don't know anything about it ,check its documention at support.sas.com  .

View solution in original post


All Replies
Trusted Advisor
Posts: 1,129

Re: BASE SAS

Could you please provide more details of the exposure limits. How the exposure limit is linked with the collateralid
Thanks,
Jag
Super User
Posts: 17,836

Re: BASE SAS

This isn't an easy problem to solve by the way, so it's an interesting problem from that perspective. It looks like a recursive chain look up and I think you can use the macro here. From a graph theory lens, it's asking to identify each branch. 

 

https://gist.github.com/statgeek/14e3aa2a9f718f551cd98134e9ceed30

 

Or search 'recursive lookup' on here and you'll find some hash solutions. 

 

Super User
Posts: 9,681

Re: BASE SAS

I have answered this question before. Isn't it you ? 
Here  could give you a start. Once you got WANT table. It is easy to  get what you want.







data have;
infile cards ;
input from $  to $ ;
cards;
1     2
1     3
4     5
5     2
9     4
6     7
8     7
;
run;
data full;
  set have end=last;
  if _n_ eq 1 then do;
   declare hash h();
    h.definekey('node');
     h.definedata('node');
     h.definedone();
  end;
  output;
  node=from; h.replace();
  from=to; to=node;
  output;
  node=from; h.replace();
  if last then h.output(dataset:'node');
  drop node;
run;


data want(keep=node household);
declare hash ha(ordered:'a');
declare hiter hi('ha');
ha.definekey('count');
ha.definedata('last');
ha.definedone();
declare hash _ha(hashexp: 20);
_ha.definekey('key');
_ha.definedone();

if 0 then set full;
declare hash from_to(dataset:'full(where=(from is not missing and to is not missing))',hashexp:20,multidata:'y');
 from_to.definekey('from');
 from_to.definedata('to');
 from_to.definedone();

if 0 then set node;
declare hash no(dataset:'node');
declare hiter hi_no('no');
 no.definekey('node');
 no.definedata('node');
 no.definedone();
 

do while(hi_no.next()=0);
 household+1; output;
 count=1;
 key=node;_ha.add();
 last=node;ha.add();
 rc=hi.first();
 do while(rc=0);
   from=last;rx=from_to.find();
   do while(rx=0);
     key=to;ry=_ha.check();
      if ry ne 0 then do;
       node=to;output;rr=no.remove(key:node);
       key=to;_ha.add();
       count+1;
       last=to;ha.add();
      end;
      rx=from_to.find_next();
   end;
   rc=hi.next();
end;
ha.clear();_ha.clear();
end;
stop;
run;





Contributor
Posts: 63

Re: BASE SAS

I dont have knowledge of hash tables,its abit difficult to understand,cud u pls brief me what is happening there actually

Solution
4 weeks ago
Super User
Posts: 9,681

Re: BASE SAS

NO. I am not going to talk about Hash Table here. It is a very big topic.

If you don't know anything about it ,check its documention at support.sas.com  .

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 186 views
  • 0 likes
  • 4 in conversation