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!
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?
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
Result:
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?
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:
options validvarname=any;
When you run the import step.
Chris
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'
270 dbms=xlsx
271 out=example
272 replace
273 ;
274 run;
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 ;
select catx('=',name,substr(put(input(substr(name,2),8.)+'01JAN1900'd-2,date9.),3))
into :rename
from contents
where name like '^_%' escape '^'
;
quit;
have you attempted to just format as a date object; and format how you like after doing this?
Proc print data=work.a;
var date;
format data mmddyy10.;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.