How to replace values for a few variables?

[NonSleeper]

The title is effectively vague, but the data looks like this.

Data set 1:

Org1     Org2     Org3     Org4     Org5

a1          a2          a3         a4          a5

b1          a5          b3          d2          b5

and so on...

Data set 2:

Org     OrgID

a1          a001

a2          a002

a3          a003

a4          a004

a5          a005

b1          b001

b3          b003

What it means is, data set 1 consists of the names of the organizations, and data set 2 consists of the codes of them. The question is: how can I assign these codes from data set 2 to data set 1? I can do a few data steps on this but that sounds a bit ineffective. Maybe a SQL thing is better but I'm illiterate of that.

[MadhuKorni]

Share your desired output format.

[RW9 · Accepted solution]

As above, post desired output.  Also post test data in a way that we don't have to type it out by hand.  At a guess you could do:

proc sql;

     create table WANT as

     select     strip(A.ORG1)||" ["||(select ORGID from HAVE2 where ORG=A.ORG1)||"]" as ORG1,

                     strip(A.ORG2)||" ["||(select ORGID from HAVE2 where ORG=A.ORG2)||"]" as ORG2,

...

     from         HAVE1 A;

quit;

This would give you:

Org1           Org2

a1 [a001]   a2 [a002]...

Although I would advise you re-organise the first dataset into a normalised format then you can just merge:

Org_No          Org

1                       a1

2                       a2

...

[Kurt_Bremser]

*Create a format from Dataset2;

data _null_;

set dataset2 end=done;

if _n_ = 1 then call execute('proc format library=work;value $orgs ');

call execute ('"' !! trim(org) !! '" = "' !! trim(orgid) !!'" ');

if done then call execute(';run;');

run;

*apply that format to dataset1;

data want;

set dataset1;

%macro assign_format;

%do i = 1 %to 5;

org_id&i = put(org&i,$orgs.);

%end;

%mend;

%assign_format;

run;

[AmitRathore]

Hi Kurt,

Very impressive program. I am pasting a simple version of your program. I hope this will also work.

data data2;

  infile datalines;

  input org$ orgid$;

datalines;

a1 a001

a2 a002

a3 a003

a4 a004

a5 a005

b1 b001

b2 b002

b3 b003

b4 b004

b5 b005

;

run;

data data2fmt;

  keep start label fmtname;

  retain fmtname '$orgfmt';

  set data2(rename = (org = start orgid = label));

run;

proc format library = work cntlin = work.data2fmt; run;

data data3;

  infile 'C:\temp\org.txt';

  input org1 $ org2 $ org3 $ org4 $ org5 $;

  format org1-org5 $orgfmt.;

run;

[Kurt_Bremser]

I just used the call execute method because I knew the exact syntax for proc format/value from memory, but couldn't reliably recall the correct layout of the cntlin file