topic Re: Reading XLSX file and change the all column type into character in SAS Data Management

Reading XLSX file and change the all column type into character

wellylly — Wed, 26 Dec 2018 07:15:09 GMT

Hi all..

I have an excel file, and i have imported it into dataset in SAS DI (race) ..
But, i got a problem when i exported it and registered the table to another SAS DI (local).
When i open the dataset in my local, the column type has changed from character to numeric meanwhile the real type is character in the first SAS DI (race) and in the excel is general type,
I dont know why but i'm afraid that one day i'll receive different format (character/numeric) from the excel file because it will generate monthly.
Then, i decided to make the all column being character format so i think it would be easy for mapping the columns.

My Question :
How to change the all of column type to be character when i import the excel file in sas?

I'd be thankful if you could help me to solve this problem.. 🙂

Re: Reading XLSX file and change the all column type into character

Kurt_Bremser — Wed, 26 Dec 2018 12:38:03 GMT

DO NOT use Excel files for reliable, production-type data transfer. NEVER.

Save your data to a csv, and read that. The data step will determine all attributes.

Excel files involve guessing, and attributes depend on the current state of the content; therefore the outcome is never reliably the same.

Re: Reading XLSX file and change the all column type into character

Ksharp — Wed, 26 Dec 2018 12:40:46 GMT

After importing Excel file, you can use the following code to change Character variable into Numeric variable.

data have;
 set sashelp.class;
run;


proc sql noprint;
select cats(name,'=_',name) into :rename separated by ' '
 from dictionary.columns
  where libname='WORK' and memname='HAVE' and type='num';

select cats(name,'=put(_',name,', best32. -l)') into :change separated by ';'
 from dictionary.columns
  where libname='WORK' and memname='HAVE' and type='num';
quit;

data want;
 set have(rename=(&rename));
 &change;
 drop _:;
 run;

Re: Reading XLSX file and change the all column type into character

wellylly — Wed, 26 Dec 2018 12:53:42 GMT

thanks Mr.Kurt for the suggestion. I will receive the excel file every month and it's not possible to me for saving it in csv file because the excel file will generate automatically..

Re: Reading XLSX file and change the all column type into character

wellylly — Wed, 26 Dec 2018 12:59:05 GMT

Thanks for the code..
But if you don't mind, could you show me a code that used to change the all variable type to be character?

Re: Reading XLSX file and change the all column type into character

Kurt_Bremser — Wed, 26 Dec 2018 13:11:22 GMT

If Excel is used to create the file automatically, it can also save to csv automatically. That is a non-issue.

Excel files are utter **** for production use.

Re: Reading XLSX file and change the all column type into character

Tom — Wed, 26 Dec 2018 14:36:45 GMT

Please explain the process flow in more detail. What it sounds like you did was

Start with an externally generated Excel file.
Import that into SAS using SAS/DI
Export a NEW Excel file from SAS/DI
Import this second Excel file into a different instance of SAS/DI.

If you are trying to move the exact same data then why not send the original file.

If you are trying to send modified data then why not send a SAS dataset instead of an Excel file.

Or perhaps the problem actually is that the first step is inconsistently generating the column types when processing different versions of the source Excel file and the second instance of SAS has nothing to do with the core issue. Instead the issue is quality control in the system that is generating the source Excel files.

I am not sure if SAS/DI has any tools to let your coerce Excel files that do not themselves enforce a standard structure into a standard structured SAS dataset. It is possible in SAS to program code to compare the structure of a dataset (for example the one you got be importing the Excel file) to a standard and when possible convert numbers to character and vice versa.

Note that Excel is NOT a database, it is a spreadsheet. If you want Excel to treat a variable as character then DO NOT set the format to GENERAL, set the format to TEXT. In general format Excel will look at the characters you enter and guess what you mean. So it will convert an ID string that happens to be all digits to a number and in doing it will lose any leading zero characters. It might also convert a string with just digits and hyphens into DATE values.

Re: Reading XLSX file and change the all column type into character

wellylly — Thu, 27 Dec 2018 07:52:21 GMT

Many thanks to answer my question..

Let me explain what i did:
1. I have an excel file with multiple sheets.
2. I used user wiritten to generate the excel file in DI using the code.

an example:

proc import datafile="/home/coba.xlsx"
out=TRS.eir
dbms=xlsx replace;
sheet="Outstanding EIR";
getnames=yes;

Then, the datasets are created.

3. I made a library then I registered the tables (datasets) into the library.
4. I made a job for mapping the datasets using Data Validation, so that I could get Portfolio Dataset.
5. But the problem is i can't make sure if my client sets the variables types (excel) as general, so sometimes it may be different every month (numeric or character), the type doesn't fix. Then, my partner suggested me to change the all variables types to character (the same way i did to txt file in DI), so it would be easy for me to map the columns without afraid if the data type has changed next month.

My question is : How to change the all variables types to character (before i map the columns)?

NB: Another experiment I've tried to change the variables types one by one to be character by added external file without the code, just import by choosing the dataset (from the sama excel file) in library before as reference table then I got the result like this picture below:

I have no idea to change the all variable types to character.. Is it possible or not? Or maybe you have another suggestion??
Thanks before..

Re: Reading XLSX file and change the all column type into character

Tom — Thu, 27 Dec 2018 13:58:16 GMT

There is not any way that I know of to tell PROC IMPORT to treat all (or any specific) column as text. It will makes its best guess based on what it sees in the column in the spreadsheet.

If you can give the users a template they will use to populate the data then you might be able to force PROC IMPORT to treat the columns as text by including a first line of data that has characters in every column. So the first two lines in your spreadsheet might look like:

VAR1|VAR2|VAR3
XXXX|XXXX|XXXX

And then just tell the users to enter their data starting in the third row.

If all of your column headers are already text then perhaps you could use the GETNAMES=NO statement on PROC IMPORT to get the same effect.

Try changing your program to something like this that will read it without names and then use the first row to rename the columns.

proc import
  datafile="/home/coba.xlsx" dbms=xlsx 
  out=nonames replace 
;
  sheet="Outstanding EIR"; 
  getnames=NO;
run;
proc transpose data=nonames(obs=1) out=names ;
  var _all_;
run;
data _null_;
  length rename $32767;
  do until (eof);
    set names end=eof;
    rename=catx(' ',rename,catx('=',_name_,nliteral(col1)));
  end;
  call symputx('rename',rename);
run;
data TRS.eir ;
  set nonames (firstobs=2);
  rename &rename ;
run;

If the column headers are messy so that they do not automatically make good variable names you might want to use this more complex method below.

proc import
  datafile="/home/coba.xlsx" dbms=xlsx 
  out=nonames replace 
;
  sheet="Outstanding EIR"; 
  getnames=NO;
run;

proc import
  datafile="/home/coba.xlsx" dbms=xlsx 
  out=names replace 
;
  sheet="Outstanding EIR"; 
  getnames=YES;
run;

proc transpose data=nonames(obs=0) out=bad_names name=from;
  var _all_;
run;
proc transpose data=names(obs=0) out=good_names name=to;
  var _all_;
run;
data _null_;
  length rename $32767;
  do until (eof);
     merge bad_names good_names end=eof;
     rename=catx(' ',rename,catx('=',from,to));
  end;
  call symputx('rename',rename);
run;
data TRS.eir ;
  set nonames (firstobs=2);
  rename &rename ;
run;

Re: Reading XLSX file and change the all column type into character

Vince_SAS — Sat, 29 Dec 2018 02:47:30 GMT

You can try using the DBSASTYPE data set option with the EXCEL or PCFILES LIBNAME engine:

libname xl excel 'C:\temp\Class.xlsx';

data work.class;
set xl.class(dbsastype=(age='char(10)' height='char(10)' weight='char(10)' ));
run;

libname xl pcfiles path='C:\temp\Class.xlsx';

data work.class;
set xl.class(dbsastype=(age='char(10)' height='char(10)' weight='char(10)' ));
run;

Vince DelGobbo

SAS R&D