CODEWITHSUNDEEP
excelsasdatasetpowerquerysas-macro
SQLSERVERbeginner
SQLSERVERbeginner

Reputation: 3

Importing tricky data into SAS dataset

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!

Upvotes: 0

Views: 42

Answers (1)

Tom
Tom

Reputation: 51566

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;

Upvotes: 0

Related Questions