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-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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