SAS to Excel using DDE, cell formatting

[ki77ku]

I am writing some data from SAS to excel using DDE. Excel is making some assumtions and displaying the data in the cells in its own assumed formats. how do I instruct excel to consider everything as text for all the columns?
Currently I am using this, but looks like it is not working.
I am using this after values are put into excel.

put '[FORMAT.NUMBER("@")]';

example: I have some value which start with dollar sign and I don't want excel to put in currency format. Like wise same thign for decimals. I have values like 8.,10.2 and so on. Excel is changing the formats.

 

[mdieckman]

I always just created a macro in excel. Have SAS launch the excel (excel has digital signature to avoid macro warning). Then have SAS call said macro and have Excel do the formatting with VBA. Have the VBA save and close, and then have SAS go back to whatever else you need to do.

 

[chika9643]

hi guys i have an assignment and unable to solve it can you please help me out


data -
33485392 ABC JEBB...... NORTHEAST BUSINESS BANKING 330000
33485392 ABC JWHA...... COMMERCIAL BKG - CARD SVCS - OPERATING 109269
10019801 PQR JTCB...... LEASING - GOVERNMENT 8218732
04660104 XYZ JTCA...... LEASING - GENERAL INDUSTRIES 93723759
33485392 ABC JTCD...... LEASING - BUSINESS BANKING 18458
10019801 PQR JTCE...... ENERGY MANAGEMENT SERVICES 721138
04660104 XYZ JTCB...... LEASING - GOVERNMENT 9219179
12345678 WWW JTCZ...... ENERGY MANAGEMENT SERVICES 71138
33485392 ABC 5SSS...... SAG-SM BUS/PREMIER SPECIAL ASSETS 36000
12345678 WWW JTXB...... LEASING - GOVERNMENT 82732
10019801 PQR JTAA...... LEASING GLOBAL INVESTMENT BANKING 63695049
12345678 WWW JTAW...... LEASING GLOBAL INVESTMENT BANKING 639
04660104 XYZ JEIG...... HIG - GOVT 91741007


Columns - customer customer_name hier hier_name outstanding

-Read data into SAS

Logic to build -
1) If customer has any hierarchies with a 5 prefix, then assign that hier for all records of that customer
2) Rest of the customers getting special treatment for assigning Hierarchies-

Special treatment hierarchies are:
JT - Leasing
JW - GPS Card Payments
EF - Card
C - Debt Capital Raising Administration
R - GMG
G - Equities

If preponderance(highest) of outstanding balance points towards any of these "special hierarchies", then the process
continues its search for the next highest outstanding balance not identified in the "special hierarchies" list.
If not other hierarchy exists, then assign hierarchy based on highest outstanding balance.

ex-
cust hier outstanding assigned_hier
case-1
101 abc... 100 xyz... comment - there are no special treatment hierarchy so we will check for preponderence
101 xyz... 300 xyz... and we come to know that xyz hier has HIGHEST outstanding bal so we have assigned
101 pqr... 109 xyz... xyz... to assigned_hier

case-2
101 abc... 100 pqr... comment - Here based on preponderence we got jtz... bcz it has highest outstanding bal
101 jtz... 300 pqr... but jtz... is in special treatment so we need to skip that and check for next
101 pqr... 109 pqr... in preponderence which is 109 (pqr...) so assigned_hier = pqr...

case-3
101 cpc... 100 jtz... comment - Here all hier (c,jt,jw) are from special treatment so we will assign based on preponderence
101 jtz... 300 jtz... which is jtz...
101 jwr... 109 jtz...