To transpose and add new row using column name

[dearkyr]

Hi All,

I need help here.

I have this data. I would like to add another 2 columns which is month and tag for 'new' and 'exist' value based on the column name.

type | group  | new_201803 | exist_201803 | new_201804 | exist_201804|
A    |Employer|          15|            21|          10|           36|
A    |Employee|          20|            17|          22|           37|
B    |Employer|           2|             8|          10|           10|
B    |Employee|          13|            20|           7|           33|

Expected data is like below:

type | group  |month  |tag        | amount|
A    |Employer|201803 |        new|     15|
A    |Employer|201803 |      exist|     21|
A    |Employer|201804 |        new|     10|
A    |Employer|201804 |      exist|     36|
A    |Employee|201803 |        new|     20|
A    |Employee|201803 |      exist|     17|
A    |Employee|201804 |        new|     22|
A    |Employee|201804 |      exist|     37|
B    |Employer|201803 |        new|      2|
B    |Employer|201803 |      exist|      8|
B    |Employer|201804 |        new|     10|
B    |Employer|201804 |      exist|     10|
B    |Employee|201803 |        new|     13|
B    |Employee|201803 |      exist|     20|
B    |Employee|201804 |        new|      7|
B    |Employee|201804 |      exist|     33|

Currently I just arranged them in manual way. I would like to use Code but don't know where to start.

Using script can allow me arranging bigger table.

Thanks in advance!

Regards

[PeterClemmensen · Accepted solution]

One way

data have;
input type $ group $ new_201803 exist_201803 new_201804 exist_201804;
datalines;
A Employer 15 21 10 36
A Employee 20 17 22 37
B Employer 2 8 10 10
B Employee 13 20 7 33
;

proc sort data=have;
	by type group;
run;

proc transpose data=have out=temp;
   by type group;
run;

data want;
	format type group month tag amount;
	set temp;
	rename COL1=Amount;
	month=input(compress(_NAME_, '', 'kd'), best.);
	tag=compress(_NAME_, '_', 'd');
	drop _NAME_;
run;

[PeterClemmensen]

Alternatively

data have;
input type $ group $ new_201803 exist_201803 new_201804 exist_201804;
datalines;
A Employer 15 21 10 36
A Employee 20 17 22 37
B Employer 2 8 10 10
B Employee 13 20 7 33
;

data want(keep=type group month tag amount);
	set have;
	array _{*} _numeric_;
	do i=1 to dim(_);
		tag=compress(vname(_[i]), '_', 'd');
		month=input(compress(vname(_[i]), '', 'kd'), best.);
		amount=_[i];
		output;
	end;
run;

[Tom]

If you had a combination of variables that uniquely identified the row then you could use PROC TRANSPOSE.

Use those variables in your BY statement for PROC TRANSPOSE.

proc transpose data=have out=step2 ;
  by type group ;
  var new_: exist_: ;
run;

Then you can parse the _NAME_ field to get the two pieces of information out of that metadata and into actual data.

data want;
  set step2;
  length month 8 tag $8  ;
  tag=scan(_name_,1,'_');
  month = input(scan(_name_,2,'_'),yymmn6.);
  format month yymm7.;
  rename col1=amount;
run;

If you don't have unique BY groups then you could just add a step to create a new variable to uniquely id each row.

data step1;
  row + 1;
  set have;
run;

Then use that in your BY statement for PROC TRANSPOSE.

proc transpose data=step1 out=step2 ;
  by row type group ;
  var new_: exist_: ;
run;