How to combine two columns in new rows in SAS?

[ph6]

Hi, I have a dataset as follows:

variable level value
-----------------------
Age_group  1    0.1
Age_group  2    0.3
Age_group  3    0.2
Age_group  4    0.5

I would like to reformat it to get,

variable        value
-------------------
(^\b Age_group)
1                0.1
2                0.3
3                0.2
4                0.5

Is there any way to perform this?

[Shmuel]

If you want to omit "variable" and rename "level" into 'variable", you can do it in the same step:

data want(rename=(level=variable));
 set have(drop=variable);
run;

[ph6]

Thanks, but I want is to combine the two columns, not only removing variable.

[Shmuel]

You can concatenate variables using cat function:

data want;
 set have(drop=variable);
   new_var = cat(strip(level), '     ',put(value, 3.1));
run;

[ph6]

I want variable to be in a separate blank row as in my question.

[Shmuel]

A sas dataset is made of columns and rows (onservations).

Each observation holds all and same columns.

Do you want it as a report? something like:

data _null_;
 set have;
      by variable;
          if first.variable then put @1 variable ;
          put  @3 level    @10 value;
run;
   

[ph6]

This made no change into my data..

[andreas_lds]

Do you want a report or a dataset?

[ph6]

I want to report this, but I am using a macro for outputing and need to have the data ready with one column which includes the variable and its levels.

[Shmuel]

Your requirement is now clear and can be done by:

data want(keeo=new_var);
 set have;
      by variable;
	    length new_var $40;
          if first.variable then new_var = variable ; else
          new_var =  left(level) || '      ' || put(value, 3.1);
run;

In case of any issue please post the log.

[ph6]

I tried your code, it works but removes the first row of the levels,

variable value
-------------------
Age_group
2 0.3
3 0.2
4 0.5

[Tom]

To have one observation become two you need to code explicit output statements.

Here is skeleton.

data want;
  set have;
  if _n_=1 then do;
       ....
       output;
  end;
  ....
  output;
run;

Replace the dots with the logic you need to generate the values you want for those output observations.

Or you could read the first observation twice.

data want;
  set sashelp.class(obs=1 keep=name rename=(name=new_var))
      sashelp.class(in=in2 keep=name age height)
  ;
  if in2 then new_var=cats(age);
  drop name age;
  rename new_var=Age;
run;

Results:

Obs    Age       Height

  1    Alfred       .
  2    14         69.0
  3    13         56.5
  4    13         65.3
  5    14         62.8
  6    14         63.5

[ghosh]

data have;
	input variable $9. level value;
	cards;
Age_group  1    0.1
Age_group  2    0.3
Age_group  3    0.2
Age_group  4    0.5
;

proc report;
	columns variable level value;
	define variable / group noprint;
	define level /display 'Variable';
	define value /display;
	Compute before variable;
		length Text $10;

		if variable="Age_group" then
			text="Age_group";
		Line @1 Text $50.;
	endcomp;
run;

[IyenJ]

Alternately, you could try creating a separate data set for the first observation. Then append/concatenate the original data set to the data set with one observation. In the below example, I'm assuming the original data set is data set two. 

Data One;

    Variable='(^\b Age_group)';

    Value=.;

    Output;

Run;

Data two;

   Set two (Drop=Variable);

   Rename Level=Variable;

Run;

Proc Append Base=One Data=Two;

Run;