03-20-2015 11:23 AM
Hello, I have an excel file (XLSX) with 72 columns representing month (i.e. Jan-14, Feb-14, etc). When I use proc import, the column header changes to sasdate: jan-14 = 41640, etc. How can I get PROC IMPORT to show Jan-14, etc as the column header/variable? Here is my code: Proc Import Datafile="&xlsx./projecttracking.xlsx" Out=others DBMS=xlsx Replace; Sheet='Data_Others'; Getnames=Yes; DATAROW=2; MIXED=Yes; GuessingRows=32797; Run; Also, I am unable to use DATAROW in the code above. Is DATAROW not compatible with DBMS=XSLX? Thank yoU!
03-20-2015 11:34 AM
I copy/paste my code from sas to notepad/MS word/used the "Advanced Editor" function on this website and none displays my code as I intended it to. Rather it's in sentence structure. What am I doing wrong?
03-20-2015 11:41 AM
Here's how I add code to a post:
- In SAS Enterprise Guide, right-click on a code selection and select Copy HTML Source to Clipboard
- In the message editor here, click HTML from the toolbar
- In the HTML view, find a snug spot after a <p></p> tag combo.
- Paste content
- Quick! Select Show Full editor again before your eyes bleed from the crazy HTML coding you see
03-20-2015 11:48 AM
Thanks Chris! I copied your suggestion over to my 'cheat sheet". Now that Chris has put my code in an easier to read format: 1. I have an excel file (XLSX) with 72 columns representing month (i.e. Jan-14, Feb-14, etc). When I use proc import, the column header changes to sasdate: jan-14 = 41640, etc. How can I get PROC IMPORT to show Jan-14, etc as the column header/variable? 2. I am unable to use DATAROW in the code above. Is DATAROW not compatible with DBMS=XSLX?
03-20-2015 03:55 PM
You might try RANGE= instead -- recommended in the documentation for DBMS=XLSX. Also, if your columns are going to have nontraditional names like "Jan-14" (with the hyphen, you'll have to set:
When you run the import step.
03-24-2015 04:57 PM
I would suggest that you do something in Excel to store character strings for your column headers instead of formatted numbers.
As an example I made a small sheet with 4 columns 'ID', 'Jan-15', 'Feb-15' and 2015-03-15 formatted to display as Mar-15.
Here is the result from PROC IMPORT.
269 proc import datafile='c:\downloads\date_example.xlsx'
NOTE: Variable Name Change. Jan-15 -> Jan_15
NOTE: Variable Name Change. Feb-15 -> Feb_15
NOTE: Variable Name Change. 42078 -> _42078
NOTE: The import data set has 1 observations and 4 variables.
NOTE: WORK.EXAMPLE data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.06 seconds
cpu time 0.03 seconds
If you cannot fix then read the column headers from the metadata (or PROC CONTENTS output) and convert to what ever format you want and generate a RENAME statement to change the variable names.
proc sql noprint ;
where name like '^_%' escape '^'
03-24-2015 02:54 PM
have you attempted to just format as a date object; and format how you like after doing this?
Proc print data=work.a;
format data mmddyy10.;