User-defined format not working in proc sql

[cheungmonica]

Hi all,

I am trying to regroup my data using various formats that I already created. However, when I put it in my proc sql code, the formats just didn't seem to work. The log shows nothing wrong when I ran the formats and the sql code and the sql code produced results with no problem (just not formatted the way i want...) I can't quite figure out what I did wrong and hope someone could provide some feedbacks!

Thank you!

Libname FORMAT '/folders/myshortcuts/myFolder';
Proc format lib=FORMAT;
value SPDlevel 1= "4"
			   2= "3"
			   3= "2"
			   4= "1"
			   5= "0"
			   .="missing"
			   other= "missing"
;
run;

Libname FORMAT '/folders/myshortcuts/myFolder';
proc format lib=FORMAT;
value race 5="missing";
run;		   

Libname FORMAT '/folders/myshortcuts/myFolder';
proc format lib=FORMAT;			   
value SPDgroup 0-12= "0"
			   13-high= "1"
			   .="missing"
			   other="missing"
;
run;

Proc sql;
create table work.adult2010 as 
select Sex,
cat(RECTYPE, SRVY_YR, HHX, FMX,FPX) as ID,
AGE_P as AgeGroup format=agegroup.,
RACERPI2 as Race format=race.,
SAD as SAD format=SPDlevel.,
NERVOUS as NERVOUS format=SPDlevel.,
RESTLESS as RESTLESS format=SPDlevel.,
HOPELESS as HOPELESS format=SPDlevel.,
EFFORT as EFFORT format=SPDlevel., 
WORTHLS as WORTHLS format=SPDlevel.,
sum(SAD, NERVOUS,RESTLESS, HOPELESS,EFFORT,WORTHLS) as K6SUM,
calculated K6SUM as SPD format=SPDgroup.
from new.adult2010;
quit;

[SASKiwi · Accepted solution]

You are missing the FMTSEARCH option to tell SAS where to pick up your new formats:

options fmtsearch = (WORK, FORMAT, LIBRARY);

This statement tells SAS to search the libraries WORK, FORMAT and LIBRARY for a format catalog called FORMATS.

BTW you only need one LIBNAME statement.

[cheungmonica]

Thank you so much! It worked right after that!