DATA Step, Macro, Functions and more

transposing and new variable creation

Accepted Solution Solved
Reply
Super Contributor
Posts: 441
Accepted Solution

transposing and new variable creation

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!


Accepted Solutions
Solution
‎02-26-2017 09:47 PM
PROC Star
Posts: 7,468

Re: transposing and new variable creation

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

 

View solution in original post


All Replies
Solution
‎02-26-2017 09:47 PM
PROC Star
Posts: 7,468

Re: transposing and new variable creation

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

 

Respected Advisor
Posts: 4,920

Re: transposing and new variable creation

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;
PG
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 137 views
  • 1 like
  • 3 in conversation