topic Re: How to replace values for a few variables? in SAS Data Management

How to replace values for a few variables?

NonSleeper — Fri, 26 Jun 2015 07:34:15 GMT

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.

Re: How to replace values for a few variables?

MadhuKorni — Fri, 26 Jun 2015 08:02:03 GMT

Share your desired output format.

Re: How to replace values for a few variables?

RW9 — Fri, 26 Jun 2015 08:44:57 GMT

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

...

Re: How to replace values for a few variables?

Kurt_Bremser — Fri, 26 Jun 2015 09:01:05 GMT

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

Re: How to replace values for a few variables?

AmitRathore — Fri, 26 Jun 2015 13:29:12 GMT

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;

Re: How to replace values for a few variables?

Kurt_Bremser — Sat, 27 Jun 2015 12:14:01 GMT

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