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.
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
...
Share your desired output format.
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
...
*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;
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.