topic Re: Insert an observation in dataset above a specified observation in SAS Programming

Insert an observation in dataset above a specified observation

mgrasmussen — Wed, 04 Aug 2021 11:06:01 GMT

Dear SAS experts

The following code inserts a new observation below the observation including the value "James" for the variable name:

data test;
set sashelp.class;
output;
if Name = "James" then do;
call missing (of _ALL_);
sex = "T1";
output;
end;
run;

Does anyone know how to insert the new observation above instead of below the specified observation?

Thank you

Best regards,

Martin

Re: Insert an observation in dataset above a specified observation

Astounding — Wed, 04 Aug 2021 14:06:51 GMT

Tricky, but not lengthy:

data want;
   do _n_=1 to totalobs;
      set have nobs=totalobs point=_n_;
      if name="James" then do;
         call missing(of _all_);
         sex='T1';
         output;
         set have point=_n_;
      end;
      output;
   end;
   stop;
run;

Make sure you include the STOP statement. Without it, the DATA step becomes an endless loop!

Re: Insert an observation in dataset above a specified observation

Ksharp — Wed, 04 Aug 2021 11:58:43 GMT

It is a 'look forward' problem.

data test;
merge sashelp.class sashelp.class(keep=name rename=(name=_name) firstobs=2);
output;
if _Name = "James" then do;
call missing (of _ALL_);
sex = "T1";
output;
end;
drop _name;
run;

Re: Insert an observation in dataset above a specified observation

mkeintz — Wed, 04 Aug 2021 15:34:52 GMT

And do you actually want to have a NAMEless observation in that record preceding your copy of the original "James" record?

Or do you want name="James" and sex="T1" and all other variables with missing values?

Re: Insert an observation in dataset above a specified observation

mgrasmussen — Thu, 05 Aug 2021 06:57:38 GMT

Hey Mkeintz

I want an observation being missing for all variables except for sex="T1". For obvious reasons this is just an example to identify a procedure which I can use in another context.

I am relatively new in SAS. I have built a quite large sociodemographic table using proc tabulate, where I have categories for multiple variables on the left (column), which are presented within categories of another variable. It is saved as a dataset. A quick illustration:

master_catvar - A | master_catvar - B - master_catvar - C

catvar1 - A

catvar1 - B

catvar1 - C

catvar2- A

catvar2 - B

catvar3 - A

catvar3 - B

catvar3 - C

catvar3 - D

I would like to introduce headlines to the catvars on the left, e.g.

catvar1:

catvar1 - A

catvar1 - B

catvar1 - C

Here, I have introduced the value (i.e. "headline") "catvar1:". Given the way the dataset is structured I needed a way to introduce said value.

I am quite sure that there are more sophisticated ways of building such tables, but the above is what I managed to construct given my current skillset. I have been told that proc report is a much more powerful tool to build sociodemographic tables as the one I am trying to build.

Thank you

Re: Insert an observation in dataset above a specified observation

Astounding — Thu, 05 Aug 2021 13:29:19 GMT

It seems you are somewhat familiar with PROC TABULATE.

The easiest solution might be to adjust the report, rather than adjusting the data. If you were to use CATVAR1, CATVAR2, etc. in the row dimension of PROC TABULATE, you would get a report in the proper structure without having to insert a blank row:

Catvar1

Catvar2

Re: Insert an observation in dataset above a specified observation

mgrasmussen — Fri, 06 Aug 2021 08:28:38 GMT

Hey Astounding

Thanks.

But could I do so while maintaining the additional categorization "column-wise" (please see my example). Could you please provide a simple example of your suggestion?

I am a bit familiar with proc tabulate but not much.

Thank you

Best regards

Re: Insert an observation in dataset above a specified observation

Astounding — Fri, 06 Aug 2021 16:22:31 GMT

No. None of the other columns would appear beside "Catvar1", "Catvar2", etc. If you would like, you could label them so that instead of "Catvar1" the row would be labeled "Catvar1 (T1)".

Re: Insert an observation in dataset above a specified observation

mgrasmussen — Mon, 09 Aug 2021 05:44:19 GMT

Okay, thanks.

I am trying to find a solution in SAS where I can create an entire sociodemographic table, i.e. a typical table 1 in a scientific paper (at least within health sciences). I have found that proc tabulate will get me far, but as you have noticed, also requires some data acrobatics.