DATA Step, Macro, Functions and more

How to merge two datasets and select value from a transposed set of data

Reply
Contributor
Posts: 74

How to merge two datasets and select value from a transposed set of data

[ Edited ]

Dear Team,

 

I have two data set .

table Achivement

location fee per casa per
1 50-75% 150-200%
2 150-200% 100-150%
3 >=200% 150-200%

 

Second table Payout Grid

  CASA 75-100% 100-150% 150-200% >=200%
fee 50-75% 0 0 3500 5250
75-100% 3000 3500 4500 7250
100-150% 3500 4500 5250 8000
150-200% 4500 5250 7250 8500
>=200% 5250 7250 8000 12500

 

Out put required 

 

location fee per casa per payout
1 50-75% 150-200% 3500
2 150-200% 100-150% 5250
3 >=200% 150-200% 8000
Super User
Super User
Posts: 7,392

Re: The SAS Support Communities Super Users Program

Hi,

 

You have not given any test data, it is a good idea to post test data in the form of a datastep so that we don't have to guess what your variable names/types are.  I have made a guess at what you want below:

 

proc sql;
  create table WANT as
  select  A.*,
          case  CASA_PER  when "100-150%" then COL2
                          when "150-200%" then COL3
                          else . end as PAYOUT
  from    ACHIEVEMENT A
  left join PAYOUT B
  on      A.FEE_PER=B.CASA;
quit;

Note that the COL3 referes to the column labelled100-150% and col3 the next as the labels given are not valid SAS names.

Super User
Posts: 9,671

Re: How to merge two datasets and select value from a transposed set of data

Second table is not real dataset . Show your data . SQL , Merge, HashTable ..... all these querying tool can do that.
Ask a Question
Discussion stats
  • 2 replies
  • 162 views
  • 0 likes
  • 3 in conversation