topic Re: lookup table in SAS Programming

lookup table

newusernew — Fri, 06 Oct 2017 05:27:31 GMT

Hi,

This is probably a question that's been discussed many times, but I will appreciate if anyone out there can kindly shed light for me.

I have a dataset (primary) contains following columns (5000 rows)

ID Proc1 Proc2 Proc3 Proc4 (........) Proc55

1 288-3 199-2 211-3 133-2 254-8 (the value for Proc1-Proc55 are string characters)

2 274-2 292-2 213-2 493-2 280-2

and another dataset (lookup) contains following (70 rows)

Proc

288-3

292-2

381-2

The aim is to create a new variable in the "primary_new" dataset: flag. If any one of the rows in the "primary" file contain a matching data as the one in the "lookup", flag=1, else flag=0.

e.g.

ID Proc1 Proc2 Proc3 Proc4 (........) Proc55 flag

1 288-3 199-2 211-3 133-2 254-8 1

2 274-2 292-2 213-2 493-2 280-2 1

3 777-2 777-1 . . . 0

I was looking at the array approach, but I am not sure whether I am in the right direction? an example code will be much appreciated.

Re: lookup table

ali_jooan — Fri, 06 Oct 2017 05:49:16 GMT

data yo;

infile datalines dsd;

length ID 8.;

length PR1 PR2 PR3 $6.;

input ID PR1 PR2 PR3;

datalines;

1,AA,JJ,SS

2,BB,KK,TT

3,CC,LL,UU

4,DD,MM,VV

5,EE,NN,WW

6,FF,AA,XX

7,GG,PP,YY

8,HH,QQ,ZZ

9,II,RR,AA

run;

DATA YO2;

SET YO;

ARRAY PR[9] PR1-PR9;

DO I=1 TO 9;

IF PR[I]='AA' THEN FLG='1';

END;

RUN;

Re: lookup table

ali_jooan — Fri, 06 Oct 2017 05:52:52 GMT

well .. you can change the array size to three, run the do loop to 3 instead of 9 and drop the iterator 'i' from the data step and should be all good.

Re: lookup table

error_prone — Fri, 06 Oct 2017 06:07:41 GMT

Posting from m mobile phone, so the following code is untested an may contain typos.

data primary_new;
Set primary;
If _n_ = 1 then do;
Declare hash h(dataset: "lookup"):
h.defineKey("proc");
h.defineDone();
End;

array p proc1-proc55;

do i = 1 to dim(p);
flag = not h.check(key: p[i]);
if flag then continue;
end;
Run;

Re: lookup table

ChrisBrooks — Fri, 06 Oct 2017 07:07:50 GMT

The first thing I'd want to do is transpose that data - it will make it much easier to work with especially when you want to do further work on it. Once you've done that a simple merge will work nicely i.e.

data have;
	length id 8 proc1 $5 proc2 $5 proc3 $5 proc4 $5;
	infile datalines;
	input id proc1 proc2 proc3 proc4;
	datalines;
1  288-3 199-2  211-3 133-2
2  274-2  292-2 213-2 493-2
;
run;

data lookup;
	length proc $5;
	infile datalines;
	input Proc;
	datalines;
288-3
292-2
381-2
;
run;

proc transpose data=have out=transposed(rename=(col1=proc _name_=procno));
	by id;
	var proc1-proc4;
run;

proc sort data=transposed;
	by proc;
run;

proc sort data=lookup;
	by proc;
run;

data want;
	merge lookup(in=a) transposed(in=b);
	by proc;
	if a and b;
run;

Re: lookup table

FredrikE — Fri, 06 Oct 2017 08:35:52 GMT

A format will do the work as well 🙂

data have;

length id 8 proc1 $5 proc2 $5 proc3 $5 proc4 $5;

input id proc1 proc2 proc3 proc4;

datalines;

1 288-3 199-2 211-3 133-2

2 274-2 292-2 213-2 493-2

;

run;

data lookup;

length proc $5;

infile datalines;

input Proc;

datalines;

288-3

292-2

381-2

;

run;

data lu;

set lookup(rename=(proc=start));

length fmtname type $32 label 8;

fmtname = 'lu';

type = 'C';

label = 1;

if _n_ = 1 then do;

output;

hlo = 'O';

label = 0;

output;

end;

else output;

run;

proc format cntlin=lu;

run;

data want;

set have;

array procs[4] proc1 - proc4;

length flag 8;

flag = 0;

do i = 1 to 4;

flag = ifn(flag=0,put(procs(i),lu.),flag);

end;

run;

//Fredrik

Re: lookup table

newusernew — Mon, 09 Oct 2017 04:57:51 GMT

hi FredrikE,

your code works perfectly fine (and much more efficient). I tried and it works. It just that I am a new user of SAS, and I don't quite understand how the code starting from ---data lu--- work.

Pardon me for the naivety as proc format is a very unfamiliar procedure for me.

Whereas the other method suggested by Chris, although it involves a bit more coding, I can see the logic behind it.

having said that, your approach is brilliant, and I would have accepted your code as solution also if the forum rules allow.

also, thank you everyone else for your contribution.

Re: lookup table

newusernew — Mon, 09 Oct 2017 04:59:59 GMT

thanks for the suggestion Chris. Your code works perfect. I changed the merge step to proc sql left join instead. seems to work fine without the need to sort the data.