transposing and new variable creation

[ilikesas]

Hi,

I have a case of complicated (at least for my level) data transformation: I need to transpose the data and create a new variable.

Putting the problem into words is also complicated, so here I will just show the data that I have and the data that I would like to obtain:

have:

Country	VarA_2001	VarA_2002	VarB_2001	VarB_2002	VarC_2001	VarC_2003
US	1	3	5	7	9	11
UK	2	4	6	8	10	12

want:

Country	VarA	VarB	VarC	year
US	1	5	9	2001
UK	2	6	10	2001
US	3	7		2002
UK	4	8		2002
US			11	2003
UK			12	2003

Thanks!

[art297 · Accepted solution]

data have;
  infile cards dlm='09'x;
  input Country	$ VarA_2001	VarA_2002	VarB_2001	VarB_2002	VarC_2001	VarC_2003;
  cards;
US	1	3	5	7	9	11
UK	2	4	6	8	10	12
;

proc transpose data=have out=need;
  by country notsorted;
run;

data need;
  set need;
  year=scan(_name_,2,'_');
  _name_=scan(_name_,1,'_');
run;

proc sort data=need;
  by year descending country;
run;

proc transpose data=need out=want (drop=_:);
  by year descending country;
  var col1;
  id _name_;
run;

Art, CEO, AnalystFinder.com

[PGStats]

Decompose, then transpose

data temp;
set have;
array v{*} VarA_2001 -- VarC_2003;
do i = 1 to dim(v);
    var = scan(vname(v{i}), 1, "_");
    year = input(scan(vname(v{i}), 2, "_"), best.);
    value = v{i};
    output;
    end;
keep country var year value;
run;

proc sort data=temp; by country year; run;

proc transpose data=temp out=want(drop=_name_);
by country year;
var value;
id var;
run;