topic Re: How to find and match value in one column to another and return as new variable in SAS Programming

How to find and match value in one column to another and return as new variable

michokwu — Thu, 14 Mar 2019 20:27:23 GMT

Hi,

I am using the sas enterprise guide

I have a dataset that contains a list of codes for first degree programs, all educational program titles and their codes ie. three columns; fd_code, program_title, pr_code

How do I look up fd_code from the pr_code column and create a new column with the corresponding program title

See below

fd_code	program_title	pr_code
2404	Aerospace Engineering	2401
5001	Anthropology And Archeology	5502
5001	Applied Mathematics	3701
2403	Architectural Engineering	2403
3701	Architecture	1401
5223	Area Ethnic And Civilization Studies	1501

Thank you

Re: How to find and match value in one column to another and return as new variable

Astounding — Thu, 14 Mar 2019 20:56:31 GMT

If you want the program title, why use FD_CODE at all? Why not just look up PROGRAM_TITLE based on PR_CODE?

Re: How to find and match value in one column to another and return as new variable

michokwu — Thu, 14 Mar 2019 21:00:14 GMT

I have individual A who has a first degree with just the fd_code given, I need to search the pr_code column to know the title of his/her first degree

Re: How to find and match value in one column to another and return as new variable

Astounding — Thu, 14 Mar 2019 21:07:33 GMT

For that, you will have to show what you want.

So far, you have shown one source of data. Clearly, you must have another source of data that you want to match with. Give a brief example of what that other data source looks like, and what the result should be after matching.

Re: How to find and match value in one column to another and return as new variable

Patrick — Thu, 14 Mar 2019 21:10:23 GMT

@michokwu wrote:

I have individual A who has a first degree with just the fd_code given, I need to search the pr_code column to know the title of his/her first degree

In the sample data you've posted an fd_code doesn't map 1:1 to a pr_code. So if you've only got the fd_code how do you think you'd be able to determine the pr_code?

5001	Anthropology And Archeology	5502
5001	Applied Mathematics	3701

Re: How to find and match value in one column to another and return as new variable

michokwu — Thu, 14 Mar 2019 21:32:38 GMT

Thank you,

Just a column with serial_no. Its from a pubic data source, so the serial number column represents each individual

Re: How to find and match value in one column to another and return as new variable

michokwu — Thu, 14 Mar 2019 21:35:03 GMT

Serial_no	fd_code	program_title	pr_code
2012000000150	2404	Aerospace Engineering	2401
2012000000194	5001	Anthropology And Archeology	5502
2012000000194	5001	Applied Mathematics	3701
2012000000295	2403	Architectural Engineering	2403
2012000000320	3701	Architecture	1401
2012000000345	5223	Area Ethnic And Civilization Studies	1501

Re: How to find and match value in one column to another and return as new variable

michokwu — Thu, 14 Mar 2019 21:39:56 GMT

The pr_code maps to the program_title.

If I need to know the program title for individual A's first degree, I will need to take the fd_code as given, and search the pr_code to come up with the program title.

My thoughts.

Re: How to find and match value in one column to another and return as new variable

ballardw — Thu, 14 Mar 2019 21:41:48 GMT

If you have your pr_code and Program_title in a data set then easiest may be to make a custom format. Below I 1) create a data set using your example data, 2) add the variables that Proc Format requires to create a format, 3) use the data set created to create format using the CNTLIN option and 4) print the original have data set values of the PR_code using the format created.

data have;
   infile datalines dlm='|';
   informat fd_code $4. program_title $50. pr_code $4.;
   input fd_code program_title pr_code ;
datalines;
2404|Aerospace Engineering|2401 
5001|Anthropology And Archeology|5502 
5001|Applied Mathematics|3701 
2403|Architectural Engineering|2403 
3701|Architecture|1401 
5223|Area Ethnic And Civilization Studies|1501 
;
run;

data temp;
   set have;
   fmtname='PROGRAM_TITLE';
   type='C';
   start=pr_code;
   label=program_title;
run;

proc format library=work cntlin=temp;
run;

Proc print data=have label;
   var pr_code;
   format pr_code $program_title.;
   label pr_code='Formatted PR_Code';
run;

Re: How to find and match value in one column to another and return as new variable

michokwu — Thu, 14 Mar 2019 22:30:15 GMT

Thanks. I received error message: ERROR: You are trying to use the character format $PROGRAM with the numeric variable Pr_Code in data set WORK.PATH.

Also, the solution does not address how to find and match the value. I tried to assign the value of pr_code to program_title but could not.

I saw a similar but not exact discussion https://communities.sas.com/t5/SAS-Programming/How-to-assign-a-column-value-based-on-another-column/td-p/217728 but still does not answer my question

Re: How to find and match value in one column to another and return as new variable

ballardw — Thu, 14 Mar 2019 23:03:28 GMT

@michokwu wrote:

Thanks. I received error message: ERROR: You are trying to use the character format $PROGRAM with the numeric variable Pr_Code in data set WORK.PATH.

Also, the solution does not address how to find and match the value. I tried to assign the value of pr_code to program_title but could not.

I saw a similar but not exact discussion https://communities.sas.com/t5/SAS-Programming/How-to-assign-a-column-value-based-on-another-column/td-p/217728 but still does not answer my question

Since you did not provide data in the form of a data step I have to guess whether values like pr_code are numeric or character. Most things named "code", like Zipcode, are identifiers and generally are not used for numeric calculation and hence are character.

Remove the quotes around the pr_code values in the format, remove the $ from both the Proc format definition and the proc print.