BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
_el_doredo
Quartz | Level 8
Hello Experts,
I am having a data in which data starts at the third line in a single cell. Suppose in A1 cell my data has 2 empty lines(lines not space). 3rd line I am having data in A1 cell.

How to remove two empty lines and keep the data in the first line of the cell

Thanks in advance
1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

Some things are easy in Excel but can be very difficult to do in SAS. It would help if we could see the SAS data set DO NOT SHOW US THE EXCEL FILE. Please show us a portion of the SAS data set as working SAS data step code (examples and instructions).

--
Paige Miller

View solution in original post

6 REPLIES 6
PeterClemmensen
Tourmaline | Level 20

SAS Datasets do not operate with 'cells'. What is your data source?

_el_doredo
Quartz | Level 8
My input data is from .xlsx format..
PaigeMiller
Diamond | Level 26

Then make the desired changes in Excel.

--
Paige Miller
_el_doredo
Quartz | Level 8
If it is for one observation means we can do manually but it is for more than 1000 observation. So looking for a function in sas
PaigeMiller
Diamond | Level 26

Some things are easy in Excel but can be very difficult to do in SAS. It would help if we could see the SAS data set DO NOT SHOW US THE EXCEL FILE. Please show us a portion of the SAS data set as working SAS data step code (examples and instructions).

--
Paige Miller
ballardw
Super User

Define "empty line". SAS data sets do not have a concept of "line" to work with.

If it means that you have a SAS data set where all of the variables are missing then something similar might work:

 

Data want;
   set have;
   if missing(var1) and missing(var2) and missing(var2) then delete;
run;

The Missing function tests if a variable is missing and returns 1/0 (true or false) for a specific observation's values. Var1, Var2 and Var3 dummy names for the first, second and third variable ('column') to see if they are missing. Add more variables if those might be missing but other variables aren't. Delete is the data step instruction to remove an observation from the output (do not write to output set).

 

OR if you know how many variables there are in your data set you could use

data want;
   set have;
   if cmiss( of _all_) = <number of variables> then delete;
run;

If the number of variables in the set is 12, as an example replace <number of variables> with 12.

 


@_el_doredo wrote:
Hello Experts,
I am having a data in which data starts at the third line in a single cell. Suppose in A1 cell my data has 2 empty lines(lines not space). 3rd line I am having data in A1 cell.

How to remove two empty lines and keep the data in the first line of the cell

Thanks in advance

 

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 6 replies
  • 562 views
  • 0 likes
  • 4 in conversation