how to combine 2 Rows and Convert it to Columns?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

how to combine 2 Rows and Convert it to Columns?

Hi Team,

I was wondering if I could get help to convert Rows to Columns. Below is the input table:

Location

 

Type Combined

 

Population

 

Collin   County, TX

 

Uninsured_Pop_Num

 

500

 

Collin   County, TX

 

Uninsured_Pop_Den

 

15000

 

Plano,   TX

 

Hispanic_Num

 

200

 

Plano,   TX

 

Hispanic_Den

 

10000

 

I would need to convert it to as follows:

Location

 

Type 

 

Numerator

 

Denominator

 

Collin   County, TX

 

Uninsured_Pop

 

500

 

15000

 

Plano,   TX

 

Hispanic_Pop

 

200

 

10000

 

Thanks in advance!


Accepted Solutions
Solution
‎11-04-2011 03:21 PM
Trusted Advisor
Posts: 1,301

how to combine 2 Rows and Convert it to Columns?

Yes, see my example, I use this:

Collin County, TX~Hispanic_Num~100

Collin County, TX~Hispanic_Den~1000

Collin County, TX~Uninsured_Pop_Num~500

Collin County, TX~Uninsured_Pop_Den~15000

Plano, TX~Hispanic_Num~200

Plano, TX~Hispanic_Den~10000

And it works...

View solution in original post


All Replies
Trusted Advisor
Posts: 1,301

how to combine 2 Rows and Convert it to Columns?

data foo;

infile cards dsd dlm='~';

input (city_state type_combined) (:$256.) population;

type=prxchange('s/_num$|_den$//i',-1,strip(type_combined));

if not(prxmatch('/_pop$/i',strip(type))) then type=strip(type)||'_Pop';

cards;

Collin County, TX~Hispanic_Num~100

Collin County, TX~Hispanic_Den~1000

Collin County, TX~Uninsured_Pop_Num~500

Collin County, TX~Uninsured_Pop_Den~15000

Plano, TX~Hispanic_Num~200

Plano, TX~Hispanic_Den~10000

;

run;

data bar;

set foo;

by city_state type;

retain type numerator denominator;

if upcase(scan(type_combined,-1,'_'))='NUM' then numerator=population;

else denominator=population;

if last.type then output;

drop type_combined population;

run;

Super User
Super User
Posts: 7,076

how to combine 2 Rows and Convert it to Columns?

You could use PROC TRANSPOSE.

data need ;

   set have ;

  _name_ = scan(type_combined,-1,'_');

  type=substr(type_combined,1,length(type_combined)-length(_name_)-1);

run;

proc transpose data=need out=want ;

   by location type;

   var population;

   id _name_;

run;

Occasional Contributor
Posts: 16

Re: how to combine 2 Rows and Convert it to Columns?

Thanks Tom and FriedEgg for your help!

Just curious - my City_State is repeated several times and could I still use same code to get all the the input rows?

Collin County, TX~Hispanic_Num~100

Collin County, TX~Hispanic_Den~1000

Collin County, TX~Uninsured_Pop_Num~500

Collin County, TX~Uninsured_Pop_Den~15000

Collin County, TX~Hispanic_Male_Num~200

Collin County, TX~Hispanic_Male_Den~1000

Collin County, TX~Uninsured_Female_Num~300

Collin County, TX~Uninsured_Female_Den~15000

Thanks again!

Roger

Solution
‎11-04-2011 03:21 PM
Trusted Advisor
Posts: 1,301

how to combine 2 Rows and Convert it to Columns?

Yes, see my example, I use this:

Collin County, TX~Hispanic_Num~100

Collin County, TX~Hispanic_Den~1000

Collin County, TX~Uninsured_Pop_Num~500

Collin County, TX~Uninsured_Pop_Den~15000

Plano, TX~Hispanic_Num~200

Plano, TX~Hispanic_Den~10000

And it works...

🔒 This topic is solved and locked.

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

Discussion stats
  • 4 replies
  • 229 views
  • 8 likes
  • 3 in conversation