## how to combine 2 Rows and Convert it to Columns?

Solved
Occasional Contributor
Posts: 16

# 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,318

## 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...

All Replies
Trusted Advisor
Posts: 1,318

## 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
Posts: 8,114

## 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,318

## 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
• 268 views
• 8 likes
• 3 in conversation