BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AJS1
Obsidian | Level 7

I have a column named "County" that lists the abbreviations for each county.  I want to replace the county abbreviations with the full name but can't figure out how to do so (If-then, Macro....???).  

 

I have taken the SAS essentials 1 course but am still struggling with writing basic code.  Any suggestions on a reference that may be helpful? 

1 ACCEPTED SOLUTION

Accepted Solutions
Panagiotis
SAS Employee

Multiple ways

 

1. IF THEN like you mentioned. PG1 level.

2. Using a format. PG2 level.

3. Using a JOIN. SQL

 

I completed 1 an 2 for you. That should get you going. Depending on the number of unique values, i'd prefer one way over the other.

 

If I had only a few countries and don't need to reuse the code i'd probably just use an if/then.

If I need to use this functionality more often, or I have a large list of unique values, i'd use option 2 or 3.

 

/*table with country abbreviations*/
data ctry;
	infile datalines;
	input Country:$3.;
	datalines;
US
GR
CA
;
run;


*************************************;
* IF THEN SOLUTION	(PG1 Level)     *;
*************************************;
data newtable;
	set ctry;
	if Country='US' then FullName = 'United States';
		else if Country='GR' then FullName='Greece';
		else if Country='CA' then FullName='Canada';
run;
/*You can change the FullName column to 'Country' if you want to overwrite the existing value*/


***************************************************;
* More Efficient Solution if want to reuse the    *;
* functionality, or you have a long country list  *;
* (PG2 Level Topics)                              *;
***************************************************;

/*Google to find a country abbreviations lookup table online. Probably find a csv version or webpage. 
Here is what you would do with that table after it's a SAS data set*/

/*Create a table of the list of countries and abbreviations from online file. I am creating this manually
because I didn't want to lookup an entire list*/
data ctryLookup;
	infile datalines dsd;
	input CtryAbb:$3. CountryName:$30.;
	datalines;
US, United States
GR, Greece
CA, Canada
;
run;


/*Once you find a table list, you need to create the following columns*/
/* 
   -start  (value to look for)
   -label  (how to label that value found)
   -fmtname (name of the format to create)
*/
data formatInputTBL;
	set ctryLookup(rename=(CtryAbb=Start
						   CountryName=Label));
	fmtName='$ctryFormat';
run;

/*Create a format using the above table as input and the CNTLIN option to create a format from a table*/
proc format cntlin=work.formatInputTBL;
run;

/*View the new format you created*/
proc format lib=work;
	select $ctryformat;
run;

/*Use that format to create a new column (or overwrite an existing column)*/
data Newtable;
	set ctry;
	Fullname = put(Country, $ctryformat.);
run;


 

 - Peter

View solution in original post

7 REPLIES 7
Panagiotis
SAS Employee

Multiple ways

 

1. IF THEN like you mentioned. PG1 level.

2. Using a format. PG2 level.

3. Using a JOIN. SQL

 

I completed 1 an 2 for you. That should get you going. Depending on the number of unique values, i'd prefer one way over the other.

 

If I had only a few countries and don't need to reuse the code i'd probably just use an if/then.

If I need to use this functionality more often, or I have a large list of unique values, i'd use option 2 or 3.

 

/*table with country abbreviations*/
data ctry;
	infile datalines;
	input Country:$3.;
	datalines;
US
GR
CA
;
run;


*************************************;
* IF THEN SOLUTION	(PG1 Level)     *;
*************************************;
data newtable;
	set ctry;
	if Country='US' then FullName = 'United States';
		else if Country='GR' then FullName='Greece';
		else if Country='CA' then FullName='Canada';
run;
/*You can change the FullName column to 'Country' if you want to overwrite the existing value*/


***************************************************;
* More Efficient Solution if want to reuse the    *;
* functionality, or you have a long country list  *;
* (PG2 Level Topics)                              *;
***************************************************;

/*Google to find a country abbreviations lookup table online. Probably find a csv version or webpage. 
Here is what you would do with that table after it's a SAS data set*/

/*Create a table of the list of countries and abbreviations from online file. I am creating this manually
because I didn't want to lookup an entire list*/
data ctryLookup;
	infile datalines dsd;
	input CtryAbb:$3. CountryName:$30.;
	datalines;
US, United States
GR, Greece
CA, Canada
;
run;


/*Once you find a table list, you need to create the following columns*/
/* 
   -start  (value to look for)
   -label  (how to label that value found)
   -fmtname (name of the format to create)
*/
data formatInputTBL;
	set ctryLookup(rename=(CtryAbb=Start
						   CountryName=Label));
	fmtName='$ctryFormat';
run;

/*Create a format using the above table as input and the CNTLIN option to create a format from a table*/
proc format cntlin=work.formatInputTBL;
run;

/*View the new format you created*/
proc format lib=work;
	select $ctryformat;
run;

/*Use that format to create a new column (or overwrite an existing column)*/
data Newtable;
	set ctry;
	Fullname = put(Country, $ctryformat.);
run;


 

 - Peter

AJS1
Obsidian | Level 7

The "If-Then" statement worked for me.  I have an excel sheet with a column for the list of abbreviations and another column with the corresponding full name.  Is there syntax I can use to incorporate that file so that I don't have to enter in 100 county names?   Alternatively, could I use:

 

libname rabdata xlsx "/folders/myfolders/sasuser.v94/2019RabData_Raw.XLS.xlsx";

data newtable;
set rabdata.rab19;
If Animal_Address_County="FRA" then Animal_Address_County="FRANKLIN";
run;

 

data newtable;

infile countyname.xlsx;

input Animal_Address_County:$30.;

datalines;

FRAN, Franklin

A, Ash

Lay, Layton;

run;

 

Reeza
Super User
If you have the data in a data set you should import that data and then use either a JOIN/MERGE or FORMAT using a CNTLIN data set to create the format. You cannot easily change that to an IF/THEN structure.
AJS1
Obsidian | Level 7

I used the MERGE function but am unclear on how to write code to include the other columns in the dataset.  Do I need to include a KEEP statement somewhere?  How would I write a FORMAT/CNTLIN function to achieve the same results (I looked it up in SAS help but still find it confusing).



libname rabdata xlsx "/folders/myfolders/sasuser.v94/2019RabData_Raw.XLS.xlsx";
libname lits2 xlsx "/folders/myfolders/sasuser.v94/LITSCodes.xlsx";


proc sort data=lits2.sheet1;
by An_Address_County;
run;

data rabdata.rab19;
merge lits2.sheet1;
by An_Address_County;
run;



Thank you!

Panagiotis
SAS Employee

Since you have the data you can follow the PROC FORMAT CNTLIN from the solution. That'll create a format from a table. That's definitely the easiest way. No way I would write 100 IF-THENS. 

 

You could also learn about SQL JOINS.

 

- Peter

Reeza
Super User
You want a format, you'll need to build a custom format though.
I think the SASHELP.ZIPCODE file will have the data you need.

This thread may be helpful.
https://communities.sas.com/t5/SAS-Procedures/Function-for-FIPS-codes-by-County/td-p/470125


https://documentation.sas.com/?docsetId=lefunctionsref&docsetTarget=n01f5qrjoh9h4hn1olbdpb5pr2td.htm...

If you have codes (FIPS) there's a bunch of functions that will do the mapping for you as well.

Panagiotis
SAS Employee

I forgot about those SASHELP tables.  Great idea.

 

- Peter

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 7 replies
  • 1340 views
  • 9 likes
  • 3 in conversation