topic Re: How to combine two columns in new rows in SAS? in SAS Programming

How to combine two columns in new rows in SAS?

ph6 — Thu, 02 Jul 2020 16:45:12 GMT

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?

Re: How to combine two columns in new rows in SAS?

Shmuel — Thu, 02 Jul 2020 17:28:39 GMT

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;

Re: How to combine two columns in new rows in SAS?

ph6 — Thu, 02 Jul 2020 17:43:00 GMT

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

Re: How to combine two columns in new rows in SAS?

Shmuel — Thu, 02 Jul 2020 17:51:36 GMT

You can concatenate variables using cat function:

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

Re: How to combine two columns in new rows in SAS?

ph6 — Thu, 02 Jul 2020 17:57:15 GMT

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

Re: How to combine two columns in new rows in SAS?

Shmuel — Thu, 02 Jul 2020 18:15:06 GMT

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;

Re: How to combine two columns in new rows in SAS?

ph6 — Thu, 02 Jul 2020 19:38:24 GMT

This made no change into my data..

Re: How to combine two columns in new rows in SAS?

andreas_lds — Fri, 03 Jul 2020 05:21:52 GMT

Do you want a report or a dataset?

Re: How to combine two columns in new rows in SAS?

ph6 — Fri, 03 Jul 2020 11:39:27 GMT

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.

Re: How to combine two columns in new rows in SAS?

Shmuel — Fri, 03 Jul 2020 12:21:38 GMT

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.

Re: How to combine two columns in new rows in SAS?

ph6 — Fri, 03 Jul 2020 15:52:59 GMT

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

Re: How to combine two columns in new rows in SAS?

Tom — Fri, 03 Jul 2020 16:35:05 GMT

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

Re: How to combine two columns in new rows in SAS?

ghosh — Fri, 03 Jul 2020 16:30:41 GMT

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;

Re: How to combine two columns in new rows in SAS?

IyenJ — Tue, 21 Feb 2023 00:57:14 GMT

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;