## Matching columns with rows

Solved
Frequent Contributor
Posts: 76

# Matching columns with rows

Have table:

 CompanyID YEAR DE DA WC Type Ratio_Type 1 1996 0.448745476 0.22336778 1.474 Max. DE 2 1 1996 0.448745476 0.22336778 1.474 Max. DA 0.5 1 1996 0.448745476 0.22336778 1.474 Min. WC 1.5 1 1996 0.448745476 0.22336778 1.474 Max. DE 2 1 1996 0.448745476 0.22336778 1.474 Max. DA 0.5 1 1996 0.448745476 0.22336778 1.474 Min. WC 1.5 1 1997 0.648033979 0.26507138 1.474 Max. DE 2 1 1997 0.648033979 0.26507138 1.474 Max. DA 0.5 1 1997 0.648033979 0.26507138 1.474 Min. WC 1.5 2 1997 0.648033979 0.26507138 1.474 Max. DE 2 2 1997 0.648033979 0.26507138 1.474 Max. DA 0.5 2 1997 0.648033979 0.26507138 1.474 Min. WC 1.5 2 1997 0.648033979 0.26507138 1.474 Max. DE 2 2 1997 0.648033979 0.26507138 1.474 Max. DA 0.5 2 1997 0.648033979 0.26507138 1.474 Min. WC 1.5 2 1997 0.648033979 0.26507138 1.474 Max. DA 0.6 2 1997 0.648033979 0.26507138 1.474 Min. WC 1.5

I would like to match the type column with the previous 3 columns and calculate the difference betwee the actual ratio and the maximum or minimum ratio.

The want table is the following:

 CompanyID YEAR DE DA WC Type Ratio_Type Difference 1 1996 0.448745476 0.22336778 1.474 Max. DE 2 1.551255 1 1996 0.448745476 0.22336778 1.474 Max. DA 0.5 0.276632 1 1996 0.448745476 0.22336778 1.474 Min. WC 1.5 -0.026 1 1996 0.448745476 0.22336778 1.474 Max. DE 2 1.551255 1 1996 0.448745476 0.22336778 1.474 Max. DA 0.5 0.276632 1 1996 0.448745476 0.22336778 1.474 Min. WC 1.5 -0.026 1 1997 0.648033979 0.26507138 1.474 Max. DE 2 1.351966 1 1997 0.648033979 0.26507138 1.474 Max. DA 0.5 0.234929 1 1997 0.648033979 0.26507138 1.474 Min. WC 1.5 -0.026 2 1997 0.648033979 0.26507138 1.474 Max. DE 2 1.351966 2 1997 0.648033979 0.26507138 1.474 Max. DA 0.5 0.234929 2 1997 0.648033979 0.26507138 1.474 Min. WC 1.5 -0.026 2 1997 0.648033979 0.26507138 1.474 Max. DE 2 1.351966 2 1997 0.648033979 0.26507138 1.474 Max. DA 0.5 0.234929 2 1997 0.648033979 0.26507138 1.474 Min. WC 1.5 -0.026 2 1997 0.648033979 0.26507138 1.474 Max. DA 0.6 0.334929 2 1997 0.648033979 0.26507138 1.474 Min. WC 1.5 -0.026

Please note that the difference is calculated differently for the Min. and Max. ratios.

Accepted Solutions
Solution
‎01-15-2017 04:31 PM
PROC Star
Posts: 8,164

## Re: Matching columns with rows

Sorry, didn't see the workbook. The following should do what you want:

proc sql;
create table want as
select *,
case
when scan(type,1,' ') eq 'Max.' and scan(type,2,' ') eq 'DE' then ratio_type-DE
when scan(type,1,' ') eq 'Min.' and scan(type,2,' ') eq 'DE' then DE-ratio_type
when scan(type,1,' ') eq 'Max.' and scan(type,2,' ') eq 'DA' then ratio_type-DA
when scan(type,1,' ') eq 'Min.' and scan(type,2,' ') eq 'DA' then DA-ratio_type
when scan(type,1,' ') eq 'Max.' and scan(type,2,' ') eq 'WC' then ratio_type-WC
when scan(type,1,' ') eq 'Min.' and scan(type,2,' ') eq 'WC' then WC-ratio_type
end as difference
from have
;
quit;

HTH,

Art, CEO, AnalystFinder.com

All Replies
Frequent Contributor
Posts: 76

## Re: Matching columns with rows

If I can get the SAS SQL code for this I would really appreciate it.

PROC Star
Posts: 8,164

## Re: Matching columns with rows

Using your example data, please show how the difference is calculated (for at least one of the min and max examples)

Frequent Contributor
Posts: 76

## Re: Matching columns with rows

Sorry I attached an Excel file. Difference in cell H2=G2-C2.

Difference= the value from the row of "Ratio_Type" minus the value from the DE column if the value in Type column is equal to "Max. DE".

Solution
‎01-15-2017 04:31 PM
PROC Star
Posts: 8,164

## Re: Matching columns with rows

Sorry, didn't see the workbook. The following should do what you want:

proc sql;
create table want as
select *,
case
when scan(type,1,' ') eq 'Max.' and scan(type,2,' ') eq 'DE' then ratio_type-DE
when scan(type,1,' ') eq 'Min.' and scan(type,2,' ') eq 'DE' then DE-ratio_type
when scan(type,1,' ') eq 'Max.' and scan(type,2,' ') eq 'DA' then ratio_type-DA
when scan(type,1,' ') eq 'Min.' and scan(type,2,' ') eq 'DA' then DA-ratio_type
when scan(type,1,' ') eq 'Max.' and scan(type,2,' ') eq 'WC' then ratio_type-WC
when scan(type,1,' ') eq 'Min.' and scan(type,2,' ') eq 'WC' then WC-ratio_type
end as difference
from have
;
quit;

HTH,

Art, CEO, AnalystFinder.com

Frequent Contributor
Posts: 76

## Re: Matching columns with rows

Thanks Art:

I simplified the table and consolidated the rows.

I have the following table:

 CompanyID YEAR DE DA WC Max. DE Max. DA Min. WC 1 1996 0.448745476 0.22336778 1.474 2 1 1 1997 0.448745476 0.22336778 1.474 1 2 1996 0.648033979 0.26507138 1.474 1.5 6 7 2 1997 0.648033979 0.26507138 1.474 1

I want the table:

 CompanyID YEAR DE DA WC Max. DE Max. DA Min. WC Diff_DE Diff_DA Diff_DC 1 1996 0.448745476 0.22336778 1.474 2 1 1.551255 0.474 1 1997 0.448745476 0.22336778 1.474 1 0.551255 2 1996 0.648033979 0.26507138 1.474 1.5 6 7 0.851966 5.734929 -5.526 2 1997 0.648033979 0.26507138 1.474 1 0.734929

Is this code going to be the same? I also have the attachment.

PROC Star
Posts: 8,164

## Re: Matching columns with rows

No, but the following code would:

proc sql;
create table want as
select *,
Max_DE-DE as Diff_DE,
Max_DA-DA as Diff_DA,
WC-Min_WC as Diff_DC
from have
;
quit;

Art, CEO, AnalystFinder.com

☑ This topic is solved.