Tricky excel data to be imported to sas dataset!

[sumi_saslearner]

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!

[Patrick]

Save your Excel as .csv and then share some representative sample data so we can fully understand the data structure. Don't use any placeholders but create a "real" data structure that contains all the cases that need to be covered (like header line, intermediate empty lines etc.). Below an example how this could look like.

Order#: AMS1
Document# Doc1
John,Fury
James,Berry
Order#: AMS2
Document# Doc2
John,Doe
James,Brown

[sumi_saslearner]

data is as follows in CSV

First Name,Last Name, NPN/A#,Order Date,Quantity,Item Product Name,Item Price,User Role,User State,User Home Market,CMS
Order #: O-AMS123,,,,,,,,,,
Document #: D-AMS342,,,,,,,,,,
Kain,Flory,34566732 ,12/28/2023,1,Sample sample,$0.00 ,BRKR,LA ,,Sample1
Order #: O-AMS797,,,,,,,,,,
Document #: D-AMS562,,,,,,,,,,
James,Brown,34589732 ,12/28/2023,1,Sample sample,$0.00 ,BRKR,LA ,,Sample2
Johny,David,34589452 ,12/28/2023,1,Sample sample,$0.00 ,BRKR,LA ,,Sample3

[sumi_saslearner]

Expected Output is :

Order,Document,First Name,Last Name, NPN/A#,Order Date,Quantity,Item Product Name,Item Price,User Role,User State,User Home Market,CMS
O-AMS123,D-AMS342,Kain,Flory,34566732 ,12/28/2023,1,Sample sample,$0.00 ,BRKR,LA ,,Sample1
O-AMS797,D-AMS562,James,Brown,34589732 ,12/28/2023,1,Sample sample,$0.00 ,BRKR,LA ,,Sample2
O-AMS797,D-AMS562,Johny,David,34589452 ,12/28/2023,1,Sample sample,$0.00 ,BRKR,LA ,,Sample3

[Patrick]

Here you go

%let path     =%sysfunc(pathname(work));
%let csv_file =test.csv;

/* create sample .csv */
data _null_;
  file "&path\&csv_file";
  infile datalines4;
  input;
  put _infile_;
  datalines4;
First Name,Last Name, NPN/A#,Order Date,Quantity,Item Product Name,Item Price,User Role,User State,User Home Market,CMS
Order #: O-AMS123,,,,,,,,,,
Document #: D-AMS342,,,,,,,,,,
Kain,Flory,34566732 ,12/28/2023,1,Sample sample,$0.00 ,BRKR,LA ,,Sample1
Order #: O-AMS797,,,,,,,,,,
Document #: D-AMS562,,,,,,,,,,
James,Brown,34589732 ,12/28/2023,1,Sample sample,$0.00 ,BRKR,LA ,,Sample2
Johny,David,34589452 ,12/28/2023,1,Sample sample,$0.00 ,BRKR,LA ,,Sample3
;;;;

/* read sample .csv into SAS table */
data want;
  attrib 
    order       length=$15 informat=$15.
    document    length=$15 informat=$15.
    first_name  length=$40 informat=$40.
    last_name   length=$40 informat=$40.
    /* ...and so on ... */
    ;
  retain order document;

  infile "&path\&csv_file" truncover dsd dlm=',' firstobs=2;
  input order_ind:$8. @;
  if order_ind='Order #:' then
    do;
      input @9 order;
      input @12 document;
    end;
  input @1
    first_name
    last_name
    /* ...and so on ... */
    ;
  drop order_ind;

run;

proc print data=want;
run;

[sumi_saslearner]

Hi @Patrick ,

Thanks a lot for this!

[Ksharp · Accepted solution]

data want;
  infile datalines4 dsd truncover firstobs=2;
  input @;
length Order Document $ 80;
retain  Order Document;
if _infile_ =: 'Order' then do;Order=scan(_infile_,2,',:');delete;end;
if _infile_ =: 'Document' then do;Document=scan(_infile_,2,',:');delete;end;
input (a1-a11) (:$100.);
  datalines4;
First Name,Last Name, NPN/A#,Order Date,Quantity,Item Product Name,Item Price,User Role,User State,User Home Market,CMS
Order #: O-AMS123,,,,,,,,,,
Document #: D-AMS342,,,,,,,,,,
Kain,Flory,34566732 ,12/28/2023,1,Sample sample,$0.00 ,BRKR,LA ,,Sample1
Order #: O-AMS797,,,,,,,,,,
Document #: D-AMS562,,,,,,,,,,
James,Brown,34589732 ,12/28/2023,1,Sample sample,$0.00 ,BRKR,LA ,,Sample2
Johny,David,34589452 ,12/28/2023,1,Sample sample,$0.00 ,BRKR,LA ,,Sample3
;;;;

[sumi_saslearner]

Hi @Ksharp 

Your Solution also works perfectly.  Awesome! Thank you