topic Re: Matching Data in New SAS User

Matching Data

Jens89 — Mon, 09 Sep 2019 10:37:34 GMT

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:

desired output:

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

Re: Matching Data

PeterClemmensen — Mon, 09 Sep 2019 10:41:24 GMT

@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?

Re: Matching Data

Jens89 — Mon, 09 Sep 2019 10:45:29 GMT

@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.

Re: Matching Data

PeterClemmensen — Mon, 09 Sep 2019 10:53:11 GMT

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 🙂

Re: Matching Data

Jens89 — Mon, 09 Sep 2019 11:03:33 GMT

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;

Re: Matching Data

PeterClemmensen — Mon, 09 Sep 2019 11:09:26 GMT

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;

Re: Matching Data

Jens89 — Mon, 09 Sep 2019 11:36:27 GMT

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!

Re: Matching Data

PeterClemmensen — Mon, 09 Sep 2019 11:42:06 GMT

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
;

Re: Matching Data

Jens89 — Mon, 09 Sep 2019 11:51:52 GMT

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.

Re: Matching Data

PeterClemmensen — Mon, 09 Sep 2019 11:53:15 GMT

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;

Re: Matching Data

Jens89 — Mon, 09 Sep 2019 12:34:49 GMT

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?

Re: Matching Data

PeterClemmensen — Mon, 09 Sep 2019 12:37:26 GMT

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;

Re: Matching Data

Jens89 — Mon, 09 Sep 2019 12:49:00 GMT

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

Re: Matching Data

PeterClemmensen — Mon, 09 Sep 2019 12:51:27 GMT

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.

Re: Matching Data

novinosrin — Mon, 09 Sep 2019 13:00:51 GMT

Hello @Jens89 Sorry too late into the thread and would just add a suggestion that's meant for lazy folks like me.

Since 5,6,7 are continuous, you could write your

IN(5,6,7) = "manager"

  5:7= "manager"

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

Re: Matching Data

Jens89 — Mon, 09 Sep 2019 15:07:53 GMT

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.