DATA Step, Macro, Functions and more

Matching columns with rows

Accepted Solution Solved
Reply
Contributor
Posts: 36
Accepted Solution

Matching columns with rows

Have table:

CompanyIDYEARDEDAWCTypeRatio_Type
119960.4487454760.223367781.474Max. DE2
119960.4487454760.223367781.474Max. DA0.5
119960.4487454760.223367781.474Min. WC1.5
119960.4487454760.223367781.474Max. DE2
119960.4487454760.223367781.474Max. DA0.5
119960.4487454760.223367781.474Min. WC1.5
119970.6480339790.265071381.474Max. DE2
119970.6480339790.265071381.474Max. DA0.5
119970.6480339790.265071381.474Min. WC1.5
219970.6480339790.265071381.474Max. DE2
219970.6480339790.265071381.474Max. DA0.5
219970.6480339790.265071381.474Min. WC1.5
219970.6480339790.265071381.474Max. DE2
219970.6480339790.265071381.474Max. DA0.5
219970.6480339790.265071381.474Min. WC1.5
219970.6480339790.265071381.474Max. DA0.6
219970.6480339790.265071381.474Min. WC1.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:

CompanyIDYEARDEDAWCTypeRatio_TypeDifference
119960.4487454760.223367781.474Max. DE21.551255
119960.4487454760.223367781.474Max. DA0.50.276632
119960.4487454760.223367781.474Min. WC1.5-0.026
119960.4487454760.223367781.474Max. DE21.551255
119960.4487454760.223367781.474Max. DA0.50.276632
119960.4487454760.223367781.474Min. WC1.5-0.026
119970.6480339790.265071381.474Max. DE21.351966
119970.6480339790.265071381.474Max. DA0.50.234929
119970.6480339790.265071381.474Min. WC1.5-0.026
219970.6480339790.265071381.474Max. DE21.351966
219970.6480339790.265071381.474Max. DA0.50.234929
219970.6480339790.265071381.474Min. WC1.5-0.026
219970.6480339790.265071381.474Max. DE21.351966
219970.6480339790.265071381.474Max. DA0.50.234929
219970.6480339790.265071381.474Min. WC1.5-0.026
219970.6480339790.265071381.474Max. DA0.60.334929
219970.6480339790.265071381.474Min. WC1.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: 7,480

Re: Matching columns with rows

Posted in reply to Agent1592

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

 

View solution in original post


All Replies
Contributor
Posts: 36

Re: Matching columns with rows

Posted in reply to Agent1592

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

PROC Star
Posts: 7,480

Re: Matching columns with rows

Posted in reply to Agent1592

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

 

Contributor
Posts: 36

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: 7,480

Re: Matching columns with rows

Posted in reply to Agent1592

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

 

Contributor
Posts: 36

Re: Matching columns with rows

Thanks Art:

I simplified the table and consolidated the rows.

I have the following table:

CompanyIDYEARDEDAWCMax. DEMax. DAMin. WC
119960.4487454760.223367781.4742 1
119970.4487454760.223367781.4741  
219960.6480339790.265071381.4741.567
219970.6480339790.265071381.474 1 

I want the table:

CompanyIDYEARDEDAWCMax. DEMax. DAMin. WCDiff_DEDiff_DADiff_DC
119960.4487454760.223367781.4742 11.551255 0.474
119970.4487454760.223367781.4741  0.551255  
219960.6480339790.265071381.4741.5670.8519665.734929-5.526
219970.6480339790.265071381.474 1  0.734929 

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

PROC Star
Posts: 7,480

Re: Matching columns with rows

Posted in reply to Agent1592

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.

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

Discussion stats
  • 6 replies
  • 238 views
  • 2 likes
  • 2 in conversation