Change displayed codes into explanation for a variable

[smalek]

Hello again
I am reaching out this time to all you SAS pros for advice. I have variable with a list of codes in one file and data dictionary in another file. I would like to change it from codes to actual explanations.

For example, Referral Source is being displayed in SAS table(imported) as:
1
3
4


In another table the following is stored:
1 Internal
2 Outreach
3 Day Hospital
4 Rehab Unit
5 In-patient Unit
etc.

My desired output is:
Internal
Day Hospital
Rehab Unit

Any feedback is greatly appreciated.
Cheers

 

[dblan]

If I understand what you want correctly, you can use Proc SQL and use an inner join.

data table1;
input n_order;
datalines;
1
3
4
;
run;

data table2;
input n source $15.;
datalines;
1 Internal
2 Outreach
3 Day Hospital
4 Rehab Unit
5 In-patient Unit
;
run;

proc sql;
create table table3 as 
select a.source

from table2 as a

inner join table1 as b
on a.n = b.n_order;
quit;

Returns:

Internal
Day Hospital
Rehab Unit

 

[ChrisW75]

Alternatively you can read that data into a format and apply it. This is actually a very efficient way of joining a "small" amount of data onto a "large" dataset. I've done this with about 1000 records on my "small" dataset, and the results were faster than a join.
Using DBLANS example:

data table2;
  input start
        label $15.;
  fmtname = 'source';
datalines;
1 Internal
2 Outreach
3 Day Hospital
4 Rehab Unit
5 In-patient Unit
;
run;

proc format cntlin=table2;
run;

data table1;
input n_order;
format n_order source.;
datalines;
1
3
4
8
;
run;

If your n_order variable is to be a character variable, change the name to $source.
Currently if it encounters a number it doesn't know, it will return the number (as you'll see in the results). If you want it to return something else (such as "Unknown") you can use the END= option on the datastep creating the format to create an alternative entry for "other" as in the following example

data agefmt;
  set sashelp.class(rename=(NAME=start age=label)) end=eof;

  fmtname = '$age';

  output;

  if eof then
  do;
    hlo= 'O';
    start = '';
    label = 99;
    output;
  end;


run;

proc format cntlin=agefmt;
run;

data _null_;
  length myname $20;
  myname = 'Alfred';
  age = put(myname,$age.);
  put myname= age=;

  myname = 'Chris';
  age = put(myname,$age.);
  put myname= age=;

  myname = 'William';
  age = put(myname,$age.);
  put myname= age=; 


  myname = 'RONALD';
  age = put(myname,$age.);
  put myname= age=; 
run;

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[smalek]

Hey Guys
After wrestling with this problem for the past couple of days. I've decided to use proc format just becuase I found it easy to understand and code.
I would like to thank dblan and ChrisW75 for their valuable posts.
Cheers Guys