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!
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
;;;;
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
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
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
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;
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
;;;;
Hi @Ksharp
Your Solution also works perfectly. Awesome! Thank you
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.