how to combine 2 Rows and Convert it to Columns?

[Roger]

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!

[FriedEgg]

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;

[Tom]

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;

[Roger]

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

[FriedEgg · Accepted solution]

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