EXCEL to SAS adding formula

[Sultana]

I wish to get SAS output having formulas similar to excel file.
Simple formulas like '=$A$1 (Month End Date)-K10 (pdDays - Days Past Due)is there in excel. How do I write in SAS to get a new column 'Paid to' with this column

 Sending a sample code.

I have everything, just need the paid to the column in SAS calculations

Market	Branch	Officer Name	CIF	Account	Customer	Origination Amount	Balance	PD	Past Due	Days Past Due	Paid To
Bandra	889	Frances	AB_CDE123	1234567890	MR Kumar	95,000	12,345	8	90to119	93	1/27/2018

[Shmuel]

Excel term $A$1 is a constant that can be delivered to sas by a macro variable.

SAS date represent the number of days past 01/01/1960 - i.e. 0 means 01JAN1960;

If other relevant variables are given in days then the formula format is just like:

%let A1 = <any date>;   /* post your preferable date format */

data want;
  set have;
       required = input("&A1" , <informat>. )  - <relevant variable>; 
      /* the informat should fit the date format */
run;

[Sultana]

Can you just write the code for this in SAS I am not getting the date, I
need the paid date as a date

(Month End Date)-K10 (pdDays - Days Past Due)

[Reeza]

Go into Excel. Type in the formula you want. Change the style to RC notation. Place that formula in your code. We don’t have your code or know the formula or values. You also seem to be mixing up SAS variables and cell references. Using both in a formula is likely going to be problematic.

---

@Sultana wrote:
Can you just write the code for this in SAS I am not getting the date, I
need the paid date as a date

(Month End Date)-K10 (pdDays - Days Past Due)

---

[Shmuel]

---

@Sultana wrote:
Can you just write the code for this in SAS I am not getting the date, I
need the paid date as a date

(Month End Date)-K10 (pdDays - Days Past Due)

---

1) What is your code to import excel data into sas?

2) Can you get the $a$1 cell data and locate it in sas ?
    What is the variable name ?
    What is the format of that variable ? 
    What is its value ?

3) Assuming K10 is an excel cell - what is the variable name in sas? what is its format ?

4) What do you mean by "month end date" - do you need to calculate it for a given date or is it the $a$1 cell value ?

5) What are sas variable names getting "pdDays" and "Days Past Due" excell cells ?

6) Can you post a sample of the imported data ?

7) Is $a$1 cell imported to the same sas dataset with the K10 and other variables?

    What is/are the name/names of the sas dataset/s ?

[Reeza]

You can use ODS EXCEL with PROC REPORT. I think you need to use RC notation though. 

data work.test;
set sashelp.class;
attrib age2 format=3. label="Age2";
age2 = 0; * default value - formulas are not created in cells with missing values;
keep name age age2;
run;

ods excel file="C:/_localdata/temp/formula_test.xlsx" style=htmlBlue;

proc print data=test noobs label;
id name;
var age / style={TAGATTR='format:0'};
var age2 / style={TAGATTR='format:0 formula:RC[-1]*2'}; * this works;
run;

ods excel close;

---

@Sultana wrote:

I wish to get SAS output having formulas similar to excel file.
Simple formulas like '=$A$1 (Month End Date)-K10 (pdDays - Days Past Due)is there in excel. How do I write in SAS to get a new column 'Paid to' with this column

 Sending a sample code.

I have everything, just need the paid to the column in SAS calculations

Market	Branch	Officer Name	CIF	Account	Customer	Origination Amount	Balance	PD	Past Due	Days Past Due	Paid To
Bandra	889	Frances	AB_CDE123	1234567890	MR Kumar	95,000	12,345	8	90to119	93	1/27/2018

---

[RW9]

"I wish to get SAS output having formulas similar to excel file." - this is what the SAS video portal:

https://video.sas.com/

And other training materials is for, to show you how to work with SAS using SAS thinking.  Avoid thinking in Excel terms, and think using SAS.  So you create a new column, and into the column you assign the results of a logical set of functions, maths operations etc.

As you have not said what A$1$ refers to, or what is in K10 I cannot help further.