SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

How to replace values for a few variables?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 75
Accepted Solution

How to replace values for a few variables?

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.


Accepted Solutions
Solution
‎06-26-2015 04:44 AM
Super User
Super User
Posts: 7,977

Re: How to replace values for a few variables?

Posted in reply to NonSleeper

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


All Replies
Contributor
Posts: 65

Re: How to replace values for a few variables?

Posted in reply to NonSleeper

Share your desired output format.

Solution
‎06-26-2015 04:44 AM
Super User
Super User
Posts: 7,977

Re: How to replace values for a few variables?

Posted in reply to NonSleeper

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

...



Super User
Posts: 7,832

Re: How to replace values for a few variables?

Posted in reply to NonSleeper

*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;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 21

Re: How to replace values for a few variables?

Posted in reply to NonSleeper

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;

Super User
Posts: 7,832

Re: How to replace values for a few variables?

Posted in reply to AmitRathore

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

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