topic Technical issue when importing an excel file to SAS in SAS Programming

Technical issue when importing an excel file to SAS

Crystal_F — Tue, 27 May 2025 16:21:26 GMT

I encountered a technical issue when importing an Excel file into SAS. The original cell format in Excel was #,##0.00,,"M" (e.g., displaying values like 3.62M). However, upon importing the data into SAS, the format changed to NLMNY15., displaying the values as $3,617,180. I would appreciate any assistance in resolving this issue, as I aim to retain all decimal values and preserve the data as it was in its raw form. Thank you!

proc import datafile="/app/sas/users/datahave.xlsx"
    out=mockdata
    dbms=xlsx
    replace;
    getnames=yes;
run;

Re: Technical issue when importing an excel file to SAS

Tom — Tue, 27 May 2025 17:32:35 GMT

Can't you just change the format that is attached to the variable so it prints differently?

Example:

proc print;
 format _numeric_;
run;

Re: Technical issue when importing an excel file to SAS

Ksharp — Wed, 28 May 2025 01:24:09 GMT

1) Save this excel as a CSV file and import it again as a string variable.

data want;
infile "C:\Users\xiakeshan\Documents\Downloads\mockdata.csv" dsd truncover firstobs=2;
input x : $40. April : $40.;
run;

2) Make a new format in sas and attach this dataset.

proc import datafile = "C:\Users\xiakeshan\Documents\Downloads\mockdata.xlsx" out =have replace dbms =excel ;
run;

proc format;
picture fmt(round)
low-high='000,000,000.99M'(mult=0.0001)
;
quit;
data want2;
set have;
format April_202X fmt.;
run;