topic Re: Code that finds and replaces character value in a column in New SAS User

Code that finds and replaces character value in a column

AJS1 — Mon, 10 Feb 2020 18:49:36 GMT

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?

Re: Code that finds and replaces character value in a column

Panagiotis — Mon, 10 Feb 2020 19:22:27 GMT

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

Re: Code that finds and replaces character value in a column

Reeza — Mon, 10 Feb 2020 20:03:27 GMT

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&docsetVersion=9.4&locale=en

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

Re: Code that finds and replaces character value in a column

Panagiotis — Mon, 10 Feb 2020 20:47:48 GMT

I forgot about those SASHELP tables. Great idea.

- Peter

Re: Code that finds and replaces character value in a column

AJS1 — Mon, 10 Feb 2020 21:58:20 GMT

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;

Re: Code that finds and replaces character value in a column

Reeza — Tue, 11 Feb 2020 04:36:34 GMT

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.

Re: Code that finds and replaces character value in a column

Panagiotis — Tue, 11 Feb 2020 13:16:39 GMT

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

Re: Code that finds and replaces character value in a column

AJS1 — Wed, 12 Feb 2020 20:56:06 GMT

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!