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!
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...
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;
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;
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
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...
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.