BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
sumi_saslearner
Fluorite | Level 6

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!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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
;;;;

View solution in original post

7 REPLIES 7
Patrick
Opal | Level 21

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
Fluorite | Level 6

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
Fluorite | Level 6

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
Opal | Level 21

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;

Patrick_0-1712307364013.png

 

sumi_saslearner
Fluorite | Level 6

Hi @Patrick ,

 

Thanks a lot for this!

Ksharp
Super User
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
Fluorite | Level 6

Hi @Ksharp 

Your Solution also works perfectly.  Awesome! Thank you

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 1256 views
  • 2 likes
  • 3 in conversation