topic Re: Add a blank row to the data set after finding particular observation in SAS Programming

Add a blank row to the data set after finding particular observation

sahoositaram555 — Wed, 25 Sep 2019 14:26:30 GMT

Hi All,

I have a dataset(nobs=300) looks somethings like below

Hospital name	N	Mean	SE	N	Mean	SE
Royal Alfred	xx	xx	xx	Xx	Xx	xx
Sydney steamed	xx	Xx	xx	Xx	Xx	Xx
Better care	Xx	Xx	Xx	Xx	Xx	Xx
Royal Alfred	Xx	Xx	Xx	Xx	Xx	Xx
People tree	Xx	Xx	Xx	Xx	Xx	Xx
Seattle	Xx	Xx	Xx	Xx	Xx	Xx
Royal Alfred	Xx	Xx	Xx	Xx	Xx	Xx
Columbia Australia	Xx	Xx	Xx	Xx	Xx	Xx
Care Sydney	Xx	xx	Xx	xx	xx	Xx

I would like to hunt for hospital named "Royal Alfred" and add a blank row just after that without disturbing the order/other observations .

output should be something like below:

Hospital name	N	Mean	SE	N	Mean	SE
Royal Alfred	xx	xx	xx	Xx	Xx	xx

Sydney steamed	xx	Xx	xx	Xx	Xx	Xx
Better care	Xx	Xx	Xx	Xx	Xx	Xx
Royal Alfred	Xx	Xx	Xx	Xx	Xx	Xx

People tree	Xx	Xx	Xx	Xx	Xx	Xx
Seattle	Xx	Xx	Xx	Xx	Xx	Xx
Royal Alfred	Xx	Xx	Xx	Xx	Xx	Xx

Columbia Australia	Xx	Xx	Xx	Xx	Xx	Xx
Care Sydney	Xx	xx	Xx	xx	xx	Xx

Kindly share your ideas, it would be very much helpful.

Re: Add a blank row to the data set after finding particular observation

PaigeMiller — Wed, 25 Sep 2019 14:33:47 GMT

data want;
    set have;
    if hospital_name='Royal Alfred' then do;
        output;
        call missing(hospital_name);
        call missing(N);
        call missing(mean);
        /* You can type the rest of the variables, I'm too lazy */
        output;
    end;
    else output;
run;

Re: Add a blank row to the data set after finding particular observation

ballardw — Wed, 25 Sep 2019 14:33:12 GMT

Don't see a real obvious need for this but:

data want;
   set have;
   output;
   if hospitalname='Royal Alfred' then do;
      call missing(of _all_);
      output;
   end;
run;

Should work.

If you have multiple values you could use :

   if hospitalname in ('Royal Alfred' 'Seattle') then do;

Re: Add a blank row to the data set after finding particular observation

franriv — Wed, 25 Sep 2019 14:38:18 GMT

data want;
	set have;
	output;

	if 'Hospital name'n="Royal Alfred" then
	do;
		array arr1 _character_;
		array arr2 _numeric_;

		do over arr1;
			arr1="";
		end;

		do over arr2;
			arr2=.;
		end;
		output;
	end;
run;

Re: Add a blank row to the data set after finding particular observation

sahoositaram555 — Wed, 25 Sep 2019 15:17:26 GMT

Hi @PaigeMiller,
it's working well when we define call missing() statement for individual variables. but not working when call missing(of _all_).any thoughts on that? is it because i have a mixture of columns both in char
& num ?

is there any option available to do it in a more shorter way since i have close to 10 columns with a mixture of both char & num?

Re: Add a blank row to the data set after finding particular observation

sahoositaram555 — Wed, 25 Sep 2019 15:18:13 GMT

Hi @franriv, Many thanks for the code.I tried it in & out. It's not working.

Re: Add a blank row to the data set after finding particular observation

PaigeMiller — Wed, 25 Sep 2019 15:22:54 GMT

@sahoositaram555 wrote:
Hi @PaigeMiller,
it's working well when we define call missing() statement for individual variables. but not working when call missing(of _all_).any thoughts on that? is it because i have a mixture of columns both in char
& num ?

is there any option available to do it in a more shorter way since i have close to 10 columns with a mixture of both char & num?

Show us the log (all the code, not just the errors) by clicking on the {i} icon and pasting the log into the window that appears. Do not skip this step.

Also, please describe in detail what is "not working".

Re: Add a blank row to the data set after finding particular observation

ballardw — Wed, 25 Sep 2019 15:26:26 GMT

@sahoositaram555 wrote:
Hi @PaigeMiller,
it's working well when we define call missing() statement for individual variables. but not working when call missing(of _all_).any thoughts on that? is it because i have a mixture of columns both in char
& num ?

is there any option available to do it in a more shorter way since i have close to 10 columns with a mixture of both char & num?

Would have to see your actual code to comment on it.

Also

"Not working" is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the {i} to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

The example I provided with Call missing (of _all_) worked on the SASHELP.CLASS data set which contains a mix of character and numeric variables.

Re: Add a blank row to the data set after finding particular observation

sahoositaram555 — Wed, 25 Sep 2019 15:42:16 GMT

Hi @ballardw and @PaigeMiller,
My apology for not defining clearly about the statement Not working. Didn't mean to disrespect/hurt by anyway.Can't thank you enough for your help.

So @ballardw, as you have suggested in the comment:
If you have multiple values you could use :
If you have multiple values you could use :
if hospitalname in ('Royal Alfred' 'Seattle') then do;

-----
The code is not giving any error , but it is working only for the first variable means it is giving output for 'Royal Alfred' but not applying the condition for
'Seattle'.

Any inputs from you both will be highly appreciated.

Re: Add a blank row to the data set after finding particular observation

ballardw — Wed, 25 Sep 2019 21:32:20 GMT

@sahoositaram555 wrote:

Hi @ballardw and @PaigeMiller,
My apology for not defining clearly about the statement Not working. Didn't mean to disrespect/hurt by anyway.Can't thank you enough for your help.

So @ballardw, as you have suggested in the comment:
If you have multiple values you could use :
If you have multiple values you could use :
if hospitalname in ('Royal Alfred' 'Seattle') then do;

-----
The code is not giving any error , but it is working only for the first variable means it is giving output for 'Royal Alfred' but not applying the condition for
'Seattle'.

Any inputs from you both will be highly appreciated.

Code

Data

We need both to determine what may be going on.

The process with multiple values does work with the SASHELP.CLASS data set. You should have that available as part of your SAS Install.

data want;
   set sashelp.class;
   output;
   if name in ('Barbara' 'John') then do;
      call missing(of _all_);
      output;
   end;
run;

If code like above is only working for one value then you need to check spelling in either your code or data values.

These won't place a row after John:

   if name in ('Barbara' ' John') then do;
   if name in ('Barbara' 'john') then do;

The first has a typo in the code compared to the value, a leading space, the second because the equal operator requires the same case for character variables. If your data were to have a leading space such as ' John' then use of 'John' fails because it is missing the leading character.

So without exact code - yours as submitted- and the actual data set we can't diagnose things. Note that character comparisons may have issues with other characters like TAB or NULL that get imbedded in the value somewhere along the line, or inconsistent case from data entry Seattle vs SeaTtle or such.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.