BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
NonSleeper
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

...



View solution in original post

5 REPLIES 5
MadhuKorni
Quartz | Level 8

Share your desired output format.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

*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
Obsidian | Level 7

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
Super User

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 Smiley Wink

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

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.

Discussion stats
  • 5 replies
  • 1098 views
  • 1 like
  • 5 in conversation