topic Re: Proc Format- Merge in SAS Procedures

Proc Format- Merge

KarthikR — Fri, 14 Sep 2012 08:32:42 GMT

Hi ,

I am using Proc format to merge two datasets and field to be merged with is Numeric.

Below is code used.

data xx;

input num 2. ;

datalines;

15;

run;

data yy;

input num 2. text $;

datalines;

12 A

13 B

14 C

15 D;

run;

data inp;

set xx(keep =num);

fmtname= "key";

label="*";

rename num=start;

proc sort data=inp nodupkey;by start;

proc format cntlin=inp;

data match;

set yy;

if input(put(num,key.),2.)="*";

run;

proc print data=match;

I get the following error.

"Format KEY was not found or could not be loaded"

I cant figure out why I am getting this error...Can anyone help?

Thanks in advance

Re: Proc Format- Merge

shivas — Fri, 14 Sep 2012 08:55:00 GMT

Hi Karthik,

What is the output you want...any how the error can be solved by assigning the format to a library.

proc format cntlin=inp library=work;run;

Thanks,

Shiva

Re: Proc Format- Merge

AUTigers — Sat, 15 Sep 2012 15:49:06 GMT

that because if input(put(num,key.),2.)="*";

you defined key format,which cause all number to be converted to *, the Input function tried to read it as a number, it will be failed for all the number defined in your INP dataset. so no input is needed here.

data match;

set yy;

if put(num,key.) ="*";

run;

will do it.

Re: Proc Format- Merge

MikeZdeb — Sat, 15 Sep 2012 16:36:41 GMT

Hi ... that does not work because you chose a "*" as the format label. The format has no OTHER condition, so if a value is not found in the format, SAS will try to cram the two-digit number that is not found into one character, the width of the format label. Since it cannot do that, it produces an "*" which just happens to be the format label. Some examples ...

* the data;

data xx;

input num @@;

datalines;

12 13 14 15

;

* some extra values of variable NUM in data set Y;

data yy;

input num text :$1. @@;

datalines;

10 Z 12 A 13 B 14 C 15 D 16 Q 99 P

;

* make the format with an "*";

data inp;

retain fmtname 'key' label '*';

set xx(rename=(num=start));

run;

proc format cntlin=inp;

run;

* use it an look at the LOG;

data match;

set yy;

if put(num,key.) eq "*";

run;

840 data match;

841 set yy;

842 if put(num,key.) eq "*";

843 run;

NOTE: There were 7 observations read from the data set WORK.YY.

NOTE: The data set WORK.MATCH has 7 observations and 2 variables.

NOTE: At least one W.D format was too small for the number to be printed. The decimal may be shifted by the "BEST" format.

see the message about "At least one W.D format was too small ..." ... that produced an asterisk Try this ...

proc print data=yy;

format num key.;

run;

you get ...

Obs num text

1 * Z

2 * A

3 * B

4 * C

5 * D

6 * Q

7 * P

and the same LOG message ...

NOTE: There were 7 observations read from the data set WORK.YY.

NOTE: At least one W.D format was too small for the number to be printed. The decimal may be shifted by the "BEST" format.

so ... if you really want to use an asterik, add an OTHER condition to the format to take care of NUMs that are not found ...

data inp;

retain fmtname 'key' label '*';

set xx(rename=(num=start)) end=last;

output;

if last then do;

hlo = 'o';

label = '#';

output;

end;

run;

then use it and look at the LOG ... no message about "At least one W.D format was too small..."

867 data match;

868 set yy;

869 if put(num,key.) eq "*";

870 run;

NOTE: There were 7 observations read from the data set WORK.YY.

NOTE: The data set WORK.MATCH has 4 observations and 2 variables.

NOTE: DATA statement used (Total process time):

or use the new format in PROC PRINT ...

proc print data=yy;

format num key.;

run;

Obs num text

1 # Z

2 * A

3 * B

4 * C

5 * D

6 # Q

7 # P

or, just choose another label (you'll still get the LOG message but the format will work ...

data inp;

retain fmtname 'key' label '$';

set xx(rename=(num=start));

run;

then use it in the data step and look at the LOG ...

890 data match;

891 set yy;

892 if put(num,key.) eq "$";

893 run;

NOTE: There were 7 observations read from the data set WORK.YY.

NOTE: The data set WORK.MATCH has 4 observations and 2 variables.

NOTE: At least one W.D format was too small for the number to be printed. The decimal may be shifted by the "BEST" format.

you get a message, but you also get the correct observations

Re: Proc Format- Merge

KarthikR — Mon, 17 Sep 2012 06:52:33 GMT

Hi,

I tried same.. Still have the same error " The Format KEY was not found or could not be loaded".

data match;

set yy;

if put(num,key.) eq "$";

_____* I get error here*

run;

Re: Proc Format- Merge

Astounding — Mon, 17 Sep 2012 13:23:28 GMT

There is a global option called FMTSEARCH that determines where SAS will look to find formats. What is its value at the time that you try to locate the format? (PROC OPTIONS can tell you that.)

You could also verify that the search process is intact by adding a test:

proc format; value test 1='found it'; run;

data _null_;

x=5;

format x test.;

run;

This would go right after your existing PROC FORMAT.

Good luck.