Importing dataset with numbers (dates) as column headers

Accepted Solution Solved
Reply
Contributor
Posts: 35
Accepted Solution

Importing dataset with numbers (dates) as column headers

Dear All,

I would like to import an excel files in to SAS which has dates (for instance, 31.03.1958) as column headers. To my understanding, SAS doesn't accept dates or numbers in general as column headers. Is there a smart way to get around this issue?

Any help would be highly appreciated.


Accepted Solutions
Solution
‎03-06-2015 05:38 PM
Super User
Posts: 19,038

Re: Importing dataset with numbers (dates) as column headers

Running SAS 9.3 this works fine:

PROC IMPORT OUT= WORK.SAMPLE

            DATAFILE= "Z:\Consulting\sample.csv"

            DBMS=CSV REPLACE;

     GETNAMES=YES;

     DATAROW=2;

RUN;



Code in Log with correct variable names:

487    /**********************************************************************

488    *   PRODUCT:   SAS

489    *   VERSION:   9.3

490    *   CREATOR:   External File Interface

491    *   DATE:      01JAN14

492    *   DESC:      Generated SAS Datastep Code

493    *   TEMPLATE SOURCE:  (None Specified.)

494    ***********************************************************************/

495       data WORK.SAMPLE    ;

496       %let _EFIERR_ = 0; /* set the ERROR detection macro variable */

497       infile 'Z:\Consulting\sample.csv' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2

497 ! ;

498          informat Category $12. ;

499          informat _31_01_2015 best32. ;

500          informat _28_02_2015 best32. ;

501          format Category $12. ;

502          format _31_01_2015 best12. ;

503          format _28_02_2015 best12. ;

504       input

505                   Category $

506                   _31_01_2015

507                   _28_02_2015

508       ;

509       if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */

510       run;


View solution in original post

Attachment

All Replies
Super User
Posts: 19,038

Re: Importing dataset with numbers (dates) as column headers

I generally don't recommend this and storing information in column names isn't a good idea in general.

Set option validvarname to ANY, import the data and then rename the variables. Or better yet transpose to a different structure so information isn't in your column headers Smiley Happy

option validvarname=ANY;

PROC Star
Posts: 7,431

Re: Importing dataset with numbers (dates) as column headers

Mark: I think proc import will simply change them to be like: _31_03_1958

If that is acceptable, then just use proc import.

Contributor
Posts: 35

Re: Importing dataset with numbers (dates) as column headers

Thanky you for your prompt and kind replies.

@Reeza, I always try to avoid storing info in column names, but I didn't create this dataset by myself. However, I'm forced to use it. Importing this dataset into sas is just an intermediate step, as I already have code to traspose it and have a column with info about date. Exacly what you are suggesting.

By using option validvarname=ANY; and proc import, I get something close to what @Arthur Tabachneck is suggesting: _1_03_1958. That is, I'm losing the first number of the date. Is there a way to avoid this?


Or suppose I immediately transpose the dataset and I obtain a  column with values (dates) such as_1_03_1958 or _31_03_1958. Can I at least extract the month and year and then combining them into a column to obtain 195803?

Super User
Super User
Posts: 7,682

Re: Importing dataset with numbers (dates) as column headers

I am in agreement with Reeza above, using dates as column headers really isn't advisable, not just for holding data in a header row, but for actually writing code.  Are you planning on updating your code each time they add a date?  Columns need to start with an underscore or letter which is why you losing the first number, again as its not advisable to do things this way.  One option is to process the file in Excel and create a CSV as you want it, i.e. VBA script to process whats received, process and save a CSV.  Then you can write a small datastep import.  However you will still need to re-code this everytime anything changes in the Excel file.  I would still suggest though that you need to discuss this at data transfer time, i.e. in the transfer document.

Super User
Posts: 19,038

Re: Importing dataset with numbers (dates) as column headers

Mark can you post a sample file, I'm too lazy to generate one.

The behaviour you're seeing is what I'd expect if validvarname was set to V7 not ANY. 

Contributor
Posts: 35

Re: Importing dataset with numbers (dates) as column headers

@Reeza, of course!

This is how my excel file. This code obviously gives an error in SAS,

data want;

     input id 31.03.1958 ;

datalines;

100     1      

200     3;

This is what I get importing the file into SAS. I'm not sure whether the option statement is placed correctly in the code.

option validvarname=ANY;

data have;

     input id _1_03_1958 ;

datalines;

100     1      

200     3;

When I import my original txt file I'm using these lines:

option validvarname=ANY;

proc import datafile='path' out = myfile dbms=tab  replace;

run;

Btw, by running proc options option=validvarname value;run; I obtain:

Option Value Information For SAS Option VALIDVARNAME

    Value: ANY

    Scope: DMS Process

    How option value set: Options Statement


Thanks a lot for your help.

PROC Star
Posts: 7,431

Re: Importing dataset with numbers (dates) as column headers

Why do you use dbms=tab rather than dbms=excel?

Contributor
Posts: 35

Re: Importing dataset with numbers (dates) as column headers

I have problems importing xlsx files, so I always import txt.

PROC Star
Posts: 7,431

Re: Importing dataset with numbers (dates) as column headers

I still don't understand why validvarname=any produced the result you indicated, without that setting 9.4 would do it correctly with dbms=tab. Again, according to the 9.4 documentation regarding dbms=tab:

Note: If a data value in the first row in the input file is read and it contains special characters that are not valid in a SAS name, such as a blank, then SAS converts the character to an underscore. For example, the variable name Occupancy Code would become the SAS variable name Occupancy_Code. Because SAS variable names cannot begin with a number, GETNAMES= prefixes an underscore to a variable name rather than replace the value’s first character. For example, 2014.CHANGES becomes _2014_CHANGES.

Valued Guide
Posts: 2,177

Re: Importing dataset with numbers (dates) as column headers

It is very helpful when the excel libname engine delivers the actual column headers as variable labels.

Does the excel import engine not do that?

Super User
Posts: 19,038

Re: Importing dataset with numbers (dates) as column headers

And this:


option validvarname=ANY;

proc import datafile='path' out = myfile dbms=tab  replace;

getnames=yes;

datarow=2;

run;

If this doesn't work, post the log from above run as well as what version of SAS?

Solution
‎03-06-2015 05:38 PM
Super User
Posts: 19,038

Re: Importing dataset with numbers (dates) as column headers

Running SAS 9.3 this works fine:

PROC IMPORT OUT= WORK.SAMPLE

            DATAFILE= "Z:\Consulting\sample.csv"

            DBMS=CSV REPLACE;

     GETNAMES=YES;

     DATAROW=2;

RUN;



Code in Log with correct variable names:

487    /**********************************************************************

488    *   PRODUCT:   SAS

489    *   VERSION:   9.3

490    *   CREATOR:   External File Interface

491    *   DATE:      01JAN14

492    *   DESC:      Generated SAS Datastep Code

493    *   TEMPLATE SOURCE:  (None Specified.)

494    ***********************************************************************/

495       data WORK.SAMPLE    ;

496       %let _EFIERR_ = 0; /* set the ERROR detection macro variable */

497       infile 'Z:\Consulting\sample.csv' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2

497 ! ;

498          informat Category $12. ;

499          informat _31_01_2015 best32. ;

500          informat _28_02_2015 best32. ;

501          format Category $12. ;

502          format _31_01_2015 best12. ;

503          format _28_02_2015 best12. ;

504       input

505                   Category $

506                   _31_01_2015

507                   _28_02_2015

508       ;

509       if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */

510       run;


Attachment
PROC Star
Posts: 7,431

Re: Importing dataset with numbers (dates) as column headers

While I don't disagree with the comments about typically NOT using this kind of variable naming structure, sometimes there simply isn't the option of having the data created differently.

I'm surprised that you got the result you indicated when using validvarname=any

My only suggestions are NOT to use that option if at all avoidable and, instead, upgrade to 9.4. According to the documentation:

Starting in SAS/ACCESS 9.4, GETNAMES= prefixes an underscore to the data value rather than replacing the value’s first character. For example, 2013.Changes becomes _2013_Changes.

Super User
Super User
Posts: 6,842

Re: Importing dataset with numbers (dates) as column headers

Seems to insert the leading _  when using SAS 9.2 on Unix.

34   proc import datafile=&fname dbms=xls replace out=read1 ;

35   run;

NOTE:    Variable Name Change.  31.03.1958 -> _31_03_1958

NOTE:    Variable Name Change.  30.04.1958 -> _30_04_1958

NOTE: The import data set has 1 observations and 3 variables.

NOTE: WORK.READ1 data set was successfully created.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 15 replies
  • 383 views
  • 6 likes
  • 6 in conversation