BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Agent1592
Pyrite | Level 9

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.

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

6 REPLIES 6
Agent1592
Pyrite | Level 9

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

art297
Opal | Level 21

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

 

Agent1592
Pyrite | Level 9

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".

 

art297
Opal | Level 21

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

 

Agent1592
Pyrite | Level 9

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.

art297
Opal | Level 21

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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