topic Re: I want to delete 2 empty lines from a column in SAS Enterprise Guide

I want to delete 2 empty lines from a column

_el_doredo — Tue, 09 May 2023 10:56:02 GMT

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

Re: I want to delete 2 empty lines from a column

PeterClemmensen — Tue, 09 May 2023 10:59:16 GMT

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

Re: I want to delete 2 empty lines from a column

_el_doredo — Tue, 09 May 2023 11:01:05 GMT

My input data is from .xlsx format..

Re: I want to delete 2 empty lines from a column

PaigeMiller — Tue, 09 May 2023 11:05:42 GMT

Then make the desired changes in Excel.

Re: I want to delete 2 empty lines from a column

_el_doredo — Tue, 09 May 2023 11:09:29 GMT

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

Re: I want to delete 2 empty lines from a column

PaigeMiller — Tue, 09 May 2023 11:22:20 GMT

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).

Re: I want to delete 2 empty lines from a column

ballardw — Tue, 09 May 2023 14:31:48 GMT

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