topic Re: Converting multiple excel rows into single dataline in SAS Programming

Converting multiple excel rows into single dataline

tennis1 — Mon, 16 Mar 2020 22:43:16 GMT

Hi All,

I currently have an excel file that has multiple lines associated with certain records as follows:

ID J		Order
		003
		01003
		05
		40
		6050

K		150
		1500005
I'm looking to create datalines in SAS as follows: `data have; input ID ORDER; datalines;J,^003\|01003\|05\|40\|6050 K,^150\|1500005;`
Also, is there a limit to how many characters can be in a single dataline? Some of the excel records have hundreds of order numbers associated with them. Thanks for all your help!

Re: Converting multiple excel rows into single dataline

Reeza — Mon, 16 Mar 2020 22:55:11 GMT

Why datalines? Wouldn't it be easier to import the Excel file and then use RETAIN to push the ID across the rows?

Re: Converting multiple excel rows into single dataline

Reeza — Mon, 16 Mar 2020 22:56:47 GMT

If you'd like to combine the rows into one, you can use the following approach:

https://gist.github.com/statgeek/d583cfa992bf56da51d435165b07e96a

@tennis1 wrote:

Hi All,

I currently have an excel file that has multiple lines associated with certain records as follows:
ID

J
Order

003

01003

05

40

6050

K 150

1500005
I'm looking to create datalines in SAS as follows:
data have;
input ID ORDER;
datalines;J,^003|01003|05|40|6050
K,^150|1500005;
Also, is there a limit to how many characters can be in a single dataline? Some of the excel records have hundreds of order numbers associated with them. Thanks for all your help!

Re: Converting multiple excel rows into single dataline

ballardw — Mon, 16 Mar 2020 23:01:03 GMT

Reading poorly structured data, such as you show, is often much easier if the file is text of some flavor. Typically CSV.

Then you don't run into the limitations of datalines statement (it sounds like you have tried pasting into data lines).

Since your data step does not run because you 1) cannot have data on the datalines statement, 2) must have the semicolon ending the data on a separate line, 3) have attempted to read your ID variable as numeric with no numeric values and 4) have defined your Order variable as numeric and tried to read data that is not numeric you get nothing but errors:

740  data have;
741  input ID ORDER;
742  datalines;

NOTE: Invalid data for ID in line 743 1-23.
NOTE: Invalid data for ORDER in line 744 1-14.
RULE:      ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+--
744        K,^150|1500005
NOTE: Invalid data errors for file CARDS occurred outside the printed range.
NOTE: Increase available buffer lines with the INFILE n= option.
ID=. ORDER=. _ERROR_=1 _N_=1
NOTE: SAS went to a new line when INPUT statement reached past the end of a line.
NOTE: The data set USER.HAVE has 1 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.00 seconds

You may want to show some examples of your data by 1) creating a CSV file by File Save As from Excel and copy/paste the top 10 rows or so into a code box opened on this forum using the {I} icon.

Then show what the expected data set should look like.

Note: most of the time making a combined variable as you show for Order is much harder to work with.

Re: Converting multiple excel rows into single dataline

ghosh — Tue, 17 Mar 2020 14:50:10 GMT

Why don't you just read in the Excel file and transpose it to have the desired result.

libname dat XLSX '~/dat/mulrows.xlsx';

data imp;
	set dat.sheet1;

	if not missing(id) then
		do;
			x=id;
			retain x;
		end;

	if missing(id) then
		id=x;
	drop x;
run;

proc transpose data=imp out=want (drop=_:);
	by id;
	var order;
run;
proc print;run;

However, if you do need the data lines, write the want dataset to a text file.

Re: Converting multiple excel rows into single dataline

Kurt_Bremser — Tue, 17 Mar 2020 07:55:07 GMT

Save your spreadsheet from Excel to a csv file.

Do NOT open this with Excel, use a text editor like Notepad++.

Copy/paste a sufficient number of lines (to illustrate your issue) into a window opened with the {i} button.

Then we can come up with valid suggestions.

ID J		Order
		003
		01003
		05
		40
		6050

K		150
		1500005
I'm looking to create datalines in SAS as follows: `data have; input ID ORDER; datalines;J,^003\|01003\|05\|40\|6050 K,^150\|1500005;`
Also, is there a limit to how many characters can be in a single dataline? Some of the excel records have hundreds of order numbers associated with them. Thanks for all your help!