CODEWITHSUNDEEP
datesas
Chelsea
Chelsea

Reputation: 1

sas: how to informat and format variable names as date

I got a data with some variable names are dates, but it cannot show date format when importing in sas. How can I format them as datetime when they are variable names?

The original Excel data like this:

acc_items name 31/12/2017 31/12/2018
COMA - OPERATING PROFIT MARGIN COMA 13344 37578
COMA - MARKET CAPITALIZATION COMA 7522 9763
COMB - OPERATING PROFIT MARGIN COMB 65456 76962
COMB - MARKET CAPITALIZATION COMB 7665 9983

I write the code as this:

proc import datafile="C:\Users\sampledata"
            dbms=xlsx replace
            out=test;
run;

But when importing in sas, the data shows as following:

acc_items name _43100 _43465
COMA - OPERATING PROFIT MARGIN COMA 13344 37578
COMA - MARKET CAPITALIZATION COMA 7522 9763
COMB - OPERATING PROFIT MARGIN COMB 65456 76962
COMB - MARKET CAPITALIZATION COMB 7665 9983

Can anyone help? Thanks a lot!

Upvotes: 0

Views: 241

Answers (1)

Tom
Tom

Reputation: 51566

When you store dates in a character field (variable names are character strings) in Excel then SAS converts them to digits that represents the internal number Excel uses to represent that date.

Since you should be storing data like dates in variables and not in metadata (variable names) just transpose the data to get the names into a variable. You can then convert the names back into dates.

proc transpose data=test out=tall;
  by acc_items name notsorted;
  var _: ;
run;

data want;
  set tall;
  date = input(compress(_name_,'_'),32.)+'30DEC1899'd ;
  format date date9.;
  rename col1 = amount;
  drop _name_;
run;

Results

  Obs              acc_items               name    amount         date

   1     COMA - OPERATING PROFIT MARGIN    COMA     13344    31DEC2017
   2     COMA - OPERATING PROFIT MARGIN    COMA     37578    31DEC2018
   3     COMA - MARKET CAPITALIZATION      COMA      7522    31DEC2017
   4     COMA - MARKET CAPITALIZATION      COMA      9763    31DEC2018
   5     COMB - OPERATING PROFIT MARGIN    COMB     65456    31DEC2017
   6     COMB - OPERATING PROFIT MARGIN    COMB     76962    31DEC2018
   7     COMB - MARKET CAPITALIZATION      COMB      7665    31DEC2017
   8     COMB - MARKET CAPITALIZATION      COMB      9983    31DEC2018

If you want to print your data in the same style as the original Excel report then use PROC REPORT and define DATE as an ACROSS variable.

proc report split='00'x data=want;
  column acc_items name amount,date;
  define acc_items/group;
  define name/group;
  define amount/sum ' ' format=comma10.;
  define date/across ' ' format=ddmmyy10.;
run;

Results

  acc_items                                 name        31/12/2017  31/12/2018
  COMA - MARKET CAPITALIZATION              COMA             7,522       9,763
  COMA - OPERATING PROFIT MARGIN            COMA            13,344      37,578
  COMB - MARKET CAPITALIZATION              COMB             7,665       9,983
  COMB - OPERATING PROFIT MARGIN            COMB            65,456      76,962

Upvotes: 1

Related Questions