CODEWITHSUNDEEP
sas
Anna
Anna

Reputation: 464

Add apostrophe in front of a number in sas

I want to add an apostrophe in front of a variable - number in sas so that the output in excel appears as a proper number and not in scientific notations

proc SQL; create table PERM.accounts as select distinct input(LOAN_ACCOUNT_NO, $30.) as 'Account No'n but this results in numbers like this in excel: 1.23456E+12 - but I need the output to look like this: 1234567891234 the way to trick excel is to put an apostrophe in front of the number like: '1234567891234 how do I code this in proc sql?

 ,input(LOAN_ACCOUNT_NO, best32.)   as 'Account No'n
,input(LOAN_ACCOUNT_NO, $30.)   as 'Account No'n

how can I append LOAN_ACCOUNT_NO using a catx statement within the proc sql function?

More code (from comment)

%SASSTARTUP; 
%macro EXPORT_MAIL (
  IN_DSNAME=PERM.FINAL_&END_CURR_MTH,
  CSV_FNAME=DATA_&END_CURR_MTH
);
  proc EXPORT 
    data=&IN_DSNAME 
    outfile="&OUTPUT_DATA_DIR.\&CSV_FNAME..csv" 
    dbms=CSV 
    replace label;
  run;
  filename myfile EMAIL 
    to=("&EMAIL_RECIP_1.") 
    from=(‘ @ ‘) 
    replyto=(‘ @ ‘)
    subject=("Sample")
    attach=(
      "&OUTPUT_DATA_DIR.\&CSV_FNAME..csv"
      content_type='application/excel'
    )
  ;

  data NULL; 
    file myfile; 
    put ' '; 
    put "The latest Sample is attached."; 
    put ' ';
  run;
%mend EXPORT_MAIL;

Upvotes: 0

Views: 587

Answers (2)

Richard
Richard

Reputation: 27508

When creating csv attachments that are to be opened with Excel, the question is more one of "Long number in CSV file appearing in scientific notation by default", which says a text formula in the csv will be honored by Excel during default Open actions of a csv.

You can also force SAS Proc Export to output a double quoted character value by appending a hard-space character (xA0) to the account number.

Example:

data have;
  acct = '123456789012345';
  accnum = input(acct,best32.);
  acctsq = cats("'",acct);

  acctweak = strip(acct)||byte(160);
  acctdqformula = cats('=TEXT(',acct,',"0")');
run;

proc export data=have outfile="c:\temp\sandbox.csv" dbms=csv replace label;
run;

options noxwait noxsync xmin;
%sysexec start "Open Excel" excel c:\temp\sandbox.csv;

So your SQL statement that creates PERM.FINAL_&END_CURR_MTH can have

, strip(LOAN_ACCOUNT_NO)||byte(160) as 'Account No'n
, cats('=TEXT(',LOAN_ACCOUNT_NO,',"0")') as 'Account No'n

or

create an Excel file for attachment

ODS EXCEL file = "&OUTPUT_DATA_DIR.\&CSV_FNAME..xlsx";
proc PRINT data=&IN_DSNAME;
ODS EXCEL CLOSE;

… attach &CSV_FNAME..xlsx with content type application/ms-excel

Upvotes: 2

SAS2Python
SAS2Python

Reputation: 1297

Try ODS instead of PROC EXPORT:

ODS tagsets.ExcelXP FILE = "DATA_&END_CURR_MTH..xml";

proc print data=PERM.FINAL_&END_CURR_MTH;
  format .....;
run;

Set your formats appropriately in the print statement.

Upvotes: 0

Related Questions