BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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.

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26
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;
--
Paige Miller

View solution in original post

9 REPLIES 9
PaigeMiller
Diamond | Level 26
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;
--
Paige Miller
sahoositaram555
Pyrite | Level 9
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?
PaigeMiller
Diamond | Level 26

@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".

--
Paige Miller
ballardw
Super User

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

sahoositaram555
Pyrite | Level 9

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.


ballardw
Super User

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

ballardw
Super User

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;
franriv
Obsidian | Level 7
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;
sahoositaram555
Pyrite | Level 9
Hi @franriv, Many thanks for the code.I tried it in & out. It's not working.

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 9 replies
  • 3597 views
  • 2 likes
  • 4 in conversation