Matching Data

[Jens89]

I have a column with different values. I want to create a new column which matches those values those specific values.

possible values I want to match the values out of my column to : 'a', 'b', 'c', 'd' (these are not in a column)

I would want to match them as following 1: 'a', 2:'b', 3:'c', 5: 'd'

column:

1

2

3

5

2

3

desired output:

a

b

c

d

b

c

at the moment I have something like:

if key= '1' then

 val = 'a'

else if key = '2' then

 val = 'b'

else if key = '3' then

 val = 'c'

else if key = '5' then

val = 'd'

I have many values so I would like to make this more efficient

thanks for your help

[PeterClemmensen]

@Jens89 hi and welcome to the SAS Community 🙂

Yes, there is a more efficient solution. 

But what value do you want eg 30 to take?

[Jens89]

@PeterClemmensen thanks!

I added an exhaustive list as an example, so all the values that I mentioned, I have a map for.

So, in this example, there would be no 30 value or any other value.

The data that I'm working with is different but I just provided a short example.

[PeterClemmensen]

Well, the data you have posted is simple, so here is a simple answer.

data have;
input x;
datalines;
1
2
3
5
2
3
;

data want;
    set have;
    y=translate(put(x, $1.), 'ABCDE', '12345');
run;

Try that out on your actual data. If you run into trouble, then don't hesitate to ask again 🙂

[Jens89]

thanks for the swift response again.

I think I need to be able to list the values.

to apply it specifically to my data. some examples of the values the numbers need to be matched to would be:

1:'analyst/technician'

2:'apprentice/to trade'

3:'Artist'

4:'Clerical;

[PeterClemmensen · Accepted solution]

Ok. You can apply a custom format like this then

proc format lib=work;
    value fmt 1='analyst/technician'
              2='apprentice/to trade'
              3='Artist'
              4='Clerical';
    ;
run;

data have;
input x;
datalines;
1
2
3
5
2
3
;

data want;
    set have;
    y=put(x, fmt. -l);
run;

[Jens89]

that seems to work great!

2 questions:

- I would need something for missing values. can I add that also in the proc format statement?

- do you have an alternative solutions where I can add this within a data step. Within my dataset I have different maps within 1 data step so I would have to create several proc steps with all the different maps. This works, but I was wondering if there is a more elegant solution.

thanks so much!

[PeterClemmensen]

No problem.

1) Yes you can add a missing value to PROC FORMAT like this

proc format lib=work;
    value fmt 1='analyst/technician'
              2='apprentice/to trade'
              3='Artist'
              4='Clerical'
              .='Missing'
    ;
run;

2) So your 'Mapping Table'.. Do you have that in a separate data set like this?

data map;
input x Occupation :$50.;
datalines;
1 analyst/technician
2 apprentice/to trade
3 Artist
4 Clerical
;

[Jens89]

2) So your 'Mapping Table'.. Do you have that in a separate data set like this?

data map;
input x Occupation :$50.;
datalines;1 analyst/technician
2 apprentice/to trade
3 Artist
4 Clerical;

I don't but I could create that.

at the moment I have one large data step which contains lots of if elseif statements to map the values like this:

if APP_ICB_WORST_EVER = 'J' then

ICB_BAND_MAPPED = 'Bad ICB';

else if APP_ICB_WORST_EVER = 'F' then

ICB_BAND_MAPPED = 'Bad ICB';

else if APP_ICB_WORST_EVER = 'W' then

ICB_BAND_MAPPED = 'Bad ICB';

if APP_ICB_VISA_WORST_EVER = 'J' then

VISA_ICB_BAND_MAPPED = 'Bad ICB';

else if APP_ICB_VISA_WORST_EVER = 'F' then

VISA_ICB_BAND_MAPPED = 'Bad ICB';

else if APP_ICB_VISA_WORST_EVER = 'W' then

VISA_ICB_BAND_MAPPED = 'Bad ICB';

else if APP_ICB_VISA_WORST_EVER = 'Wr' then

VISA_ICB_BAND_MAPPED = 'Bad ICB';

etc.

[PeterClemmensen]

Ok. Lets say that you create a mapping table like this

data map;
input x Occupation $ 3-50;
datalines;
1 analyst/technician
2 apprentice/to trade
3 Artist
4 Clerical
;

Then you can do this 

data want(drop=rc);
    if _N_=1 then do;
        declare hash h(dataset:'map');
        h.definekey('x');
        h.definedata('Occupation');
        h.definedone();
    end;

    set have;
    length Occupation $50;

    rc=h.find();
run;

[Jens89]

thanks again,

I will study this option as well. for now I think I will use the format step. One more question on that, how can I add something for "else" values?

[PeterClemmensen]

Like this

proc format lib=work;
    value fmt 1='analyst/technician'
              2='apprentice/to trade'
              3='Artist'
              4='Clerical'
              .='Missing'
          other='Unknown'
    ;
run;

data have;
input x;
datalines;
1
2
3
5
2
3
.
6
;

data want;
    set have;
    y=put(x, fmt. -l);
run;

[Jens89]

great.

2 other issues I've ran into now too.

-  If there are more keys which need to be matched to the same value eg:

proc format lib=work out=t;
    value fmt 1='analyst/technician'
              2='apprentice/to trade'
              3='Artist'
              4='Clerical'
	      IN(5,6,7) = "manager"
              .='Missing'
          other='Unknown'
    ;
run;

- in the format step lib = work. in the data step, that specific table doesn't seem to be referenced which would mean I'd run into issues with multiple proc statements. is there a way to give it a name in the proc step and reference it in the data have step?

once again, thanks so much for your help

[PeterClemmensen]

1) Do this

proc format lib=work;
    value fmt 1='analyst/technician'
              2='apprentice/to trade'
            3,7='Artist'
              4='Clerical'
              .='Missing'
          other='Unknown'
    ;
run;

data have;
input x;
datalines;
1
2
3
5
2
3
.
6
7
;

data want;
    set have;
    y=put(x, fmt. -l);
run;

2) The Lib Option in the PROC FORMAT Statement simply tells SAS where to store the Format. Noting else.

[Jens89]

2) The Lib Option in the PROC FORMAT Statement simply tells SAS where to store the Format. Noting else.

This is giving me an issue.

with the multiple proc formats, values are overriden.

I could have

1 = 'analyst/technician'

but then for another one

1 = 'agriculture'

so then it wouldn't match the right values unfortunately.