topic Re: The SAS Support Communities Super Users Program in SAS Programming

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

TarunKumar — Thu, 09 Jun 2016 08:20:32 GMT

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

Re: The SAS Support Communities Super Users Program

RW9 — Thu, 09 Jun 2016 08:19:39 GMT

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.

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

Ksharp — Thu, 09 Jun 2016 12:34:01 GMT

Second table is not real dataset . Show your data . SQL , Merge, HashTable ..... all these querying tool can do that.