Insert an observation in dataset above a specified observation

[mgrasmussen]

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

[Astounding]

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!

[Ksharp · Accepted solution]

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;

[mkeintz]

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?

[mgrasmussen]

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

[Astounding]

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

    A

    B

    C

Catvar2

    A

    B

    C

[mgrasmussen]

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

[Astounding]

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

[mgrasmussen]

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.