topic how to combine 2 Rows and Convert it to Columns? in SAS Programming

how to combine 2 Rows and Convert it to Columns?

Roger — Thu, 27 Oct 2011 18:05:47 GMT

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!

how to combine 2 Rows and Convert it to Columns?

FriedEgg — Thu, 27 Oct 2011 18:26:36 GMT

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;

how to combine 2 Rows and Convert it to Columns?

Tom — Thu, 27 Oct 2011 19:53:38 GMT

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;

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

Roger — Tue, 01 Nov 2011 03:18:44 GMT

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

how to combine 2 Rows and Convert it to Columns?

FriedEgg — Fri, 04 Nov 2011 19:21:01 GMT

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