Importing tricky data into SAS dataset

Question

I have an excel file where the data is as below

Firstname	Lastname
Order#: AMS1
Document# Doc1
John	Fury
James	Berry

I want to export this kind of excel file in SAS dataset and transform data as

Order	Document	Firstname	Lastname
AMS1	Doc1	John	Fury
AMS1	Doc1	James	Berry

Either this can be transformed in excel itself and them imported into SAS dataset or imported into SAS first and then transformed. Please help!

Answer 1

Just import the spreadsheet normally and then add a second data step to pull out the values from the FIRSTNAME variable and remember them.

So assuming you imported that XSLX file to create a SAS dataset named HAVE you could run this data step to create what you want.

  data want;
    length order $10 document $30 ;
    retain order document;
    set have;
    if lowcase(firstname)=:'order#:' then do;
       order=left(scan(firstname,2,':'));
       delete;
    end;
    if lowcase(firstname)=:'document#:' then do;
       document=left(scan(firstname,2,':'));
       delete;
    end;
  run;