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...
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.