topic Re: How to subtract two numeric values in same table different columns? in SAS Programming

How to subtract two numeric values in same table different columns?

LMSSAS — Fri, 04 Nov 2022 14:03:34 GMT

Good monring, I am trying to subtract two numeric variables (visual is below my code )from two columns, then get the percentage in another column. I want to create a column with the difference, then another column with the percentage differernce. Can someone please look at my code and tell me how to solve for this? I am getting the same answers for all rows, i added the group by and that didn't correct it.

PROC TRANSPOSE DATA = YOY OUT= OUTPUT (drop=_name_);
BY  'Submitt Date'n ;
ID  Year;
var AEP_Sales
;RUN;

Proc sql; create table AEP_Compare as
select distinct *,
sum(2021-2022) as Difference
From Output
group by 'Submitt Date'n
;quit;

Proc sql; create table AEP_CompareF as
select distinct *,
Difference/2021 as'Percentage Difference'n 
From AEP_Compare
group by 'Submitt Date'n
;quit;

Re: How to subtract two numeric values in same table different columns?

LMSSAS — Fri, 04 Nov 2022 14:51:06 GMT

Please disregard my original question. i was able to solve for this using, it was formatting issues.

Proc sql; create table AEP_Compare as
select distinct *,
'2021'n-'2022'n as Difference,
('2021'n-'2022'n)/'2021'n as'Percentage Difference'n
From Output
group by 'Submitt Date'n,'2021'n, '2022'n
;quit;

Re: How to subtract two numeric values in same table different columns?

ballardw — Fri, 04 Nov 2022 14:53:28 GMT

You have told the Sum function to "sum" the value 2021-2022, which is ALWAYS -1. SAS doesn't use variable names like 2021. Non-standard names must use the '2021'n nomenclature, just like your 'Submitt date'n.

Re: How to subtract two numeric values in same table different columns?

LMSSAS — Fri, 04 Nov 2022 16:56:10 GMT

Thank you!

Re: How to subtract two numeric values in same table different columns?

Tom — Fri, 04 Nov 2022 17:29:51 GMT

Coding will be much easier if you use NORMAL variable names.

So if you want the TRANSPOSE step this way:

PROC TRANSPOSE DATA = YOY prefix=YEAR_ 
  OUT= OUTPUT (drop=_name_ rename=('Submitt Date'n=Submit_Date ))
;
  BY  'Submitt Date'n ;
  ID  Year;
   var AEP_Sales;
RUN;

Then OUTPUT will the variables SUBMIT_DATE YEAR_2021 YEAR_2022 etc instead of the goofy names that are causing you so much trouble.