%MACRO UTLCRDN ( UTITLE=DISTINCT LEVELS OF TABLE COLUMNS, UOBJ=UTLCRDN, /*-------------------------------------*\ ! INPUTS ! ! ! ! MACRO UTLTYTL IN AUTOCALL LIB ! ! ! \*-------------------------------------*/ UIN1=D:\OBJ, UINMEM11=UTLDWMD1, /*-------------------------------------*\ ! OUTPUT TABLES ! \*-------------------------------------*/ UOT1=&UIN1, UOTMEM11=UTLCRDN1, UFLOP=01.01.01 ) /DES = "CARDINALITY OF SAS COLUMNS"; /*----------------------------------------------*\ ! ! ! Code by Roger DeAngelis ! ! ! ! SAS612 WIN95 ! ! ! ! Compucraft Inc ! ! 49 Spackenkill Rd ! ! Poughkeepsie, NY 12603 ! ! ! ! Office 914-463-2770 ! ! Fax 914-462-7595 ! ! ! ! Email xlr82sas@aol.com ! ! ! ! Users are free to do whatever they want ! ! with any or all of this code. ! ! ! ! Compucraft is not responsible for any ! ! problems associated with this code. ! ! ! ! Use at your own risk ! ! ! \*----------------------------------------------*/ /*----------------------------------------------*\ ! ! ! INPUTS ! ! ====== ! ! Any SAS table or View ! ! ! ! UIN1 = INPUT SAS DATABASE ! ! UINMEM11 - INPUT TABLE ! ! ! ! ! ! PROCESS ! ! ======= ! ! ! ! DETERMINE THE NUMBER OF DISTINCT LEVELS ! ! FOR EACH COLUMN IN INPUT SAS TABLE ! ! ! ! ! ! META DATA TABLE AND REPORT ! ! ========================= ! ! ! ! UOT1 = OUTPUT DATABASE ! ! UOTMEM11 = OUTPUT TABLE ! ! ! ! SASLIST - REPORT SEE BELOW ! ! ! \*----------------------------------------------*/ /* SAMPLE OUTPUT DATA THIS IS TESTDATA obs=255 CODE OBJECT utlcrdn LOCATED IN C:\UTL EXECUTED ON 23MAY98:21:36 EXECUTED BY 6789453907 FOREGROUND WIN_95 SAS6.12 IN -c:\windows\temp testdata OUT- c:\windows\temp datatest „ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ† ‚ LENGTH DISTINCT‚ ‚VARIABLE DESCRIPTION COLUMN TYPE BYTES LEVELS‚ ‡ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ…ƒƒƒƒƒƒƒƒƒƒƒƒƒ…ƒƒƒƒ…ƒƒƒƒƒƒƒƒ…ƒƒƒƒƒƒƒƒ‰ ‚People of the world ‚PEOPLE ‚ C ‚ 12‚ 4‚ ‡ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒˆƒƒƒƒƒƒƒƒƒƒƒƒƒˆƒƒƒƒˆƒƒƒƒƒƒƒƒˆƒƒƒƒƒƒƒƒ‰ ‚Places of the world ‚PLACES ‚ C ‚ 20‚ 5‚ ‡ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒˆƒƒƒƒƒƒƒƒƒƒƒƒƒˆƒƒƒƒˆƒƒƒƒƒƒƒƒˆƒƒƒƒƒƒƒƒ‰ ‚Things of the world ‚THINGS ‚ C ‚ 30‚ 3‚ ‡ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒˆƒƒƒƒƒƒƒƒƒƒƒƒƒˆƒƒƒƒˆƒƒƒƒƒƒƒƒˆƒƒƒƒƒƒƒƒ‰ ‚Income of the world ‚INCOME ‚ N ‚ 8‚ 9‚ Šƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ‹ƒƒƒƒƒƒƒƒƒƒƒƒƒ‹ƒƒƒƒ‹ƒƒƒƒƒƒƒƒ‹ƒƒƒƒƒƒƒƒŒ */ %LOCAL UI UDSID UOBS UVARS ULBL VARNAME VARTYPE VARLABEL VARLEN ; LIBNAME UIN1 "&UIN1"; LIBNAME UOT1 "&UOT1"; PROC DATASETS LIBRARY=UOT1 NOLIST ; DELETE &uotmem11; QUIT; /*-------------------------------------*\ ! CREATE TEMPLATE FOR APPEND ! \*-------------------------------------*/ PROC SQL; CREATE TABLE UOT1.UOTMEM11 ( TYPE=DATA LABEL="METADATA &UIN1 &UINMEM11", LABEL CHAR(40) LABEL="VARIABLE DESCRIPTION" , COLUMN CHAR(8) LABEL="COLUMN DESCRIPTION" , TYPE CHAR(1) LABEL="VARIABLE TYPE" , LENGTH CHAR(8) LABEL="VARIABLE LENGTH BYTES", COUNT INT LABEL="VARIABLE CARDINALITY" , POSITION INT LABEL="COLUMN NUMBER" ) ; QUIT; %LET UDSID = %SYSFUNC( OPEN ( UIN1.&UINMEM11, I ) ); /*-------------------------------------*\ ! GET THE NUMBER OF COLUMNS FOR LOOP ! \*-------------------------------------*/ %LET UOBS = %SYSFUNC(ATTRN(&UDSID,NLOBS)); %LET UVARS = %SYSFUNC(ATTRN(&UDSID,NVARS)); %LET ULBL = %SYSFUNC(ATTRC(&UDSID,LABEL)); %DO UI = 1 %TO &UVARS; /*-------------------------------------*\ ! GET ATTRIBUTES ! \*-------------------------------------*/ %LET UVARNAM = %SYSFUNC ( VARNAME ( &UDSID, &UI ) ); %LET UVARTYP = %SYSFUNC ( VARTYPE ( &UDSID, &UI ) ); %LET UVARLBL = %SYSFUNC ( VARLABEL ( &UDSID, &UI ) ); %LET UVARLEN = %SYSFUNC ( VARLEN ( &UDSID, &UI ) ); PROC SQL; CREATE TABLE &UVARNAM AS SELECT "&UVARLBL " AS LABEL , "&UVARNAM " AS COLUMN , "&UVARTYP " AS TYPE , "&UVARLEN " AS LENGTH , &UI AS POSITION, COUNT ( DISTINCT &UVARNAM ) AS COUNT FROM UIN1.&UINMEM11 ; QUIT; PROC APPEND BASE=UOT1.&UOTMEM11 DATA=&UVARNAM FORCE ; RUN; %END; %LET RC = %SYSFUNC ( CLOSE ( &UDSID ) ); %utltytl ( utitle=%str(&ulbl obs=&uobs), uobj=utlcrdn, uin=&uin1 &uinmem11, uot=&uot1 &uotmem11 ); proc report data=uot1.&uotmem11 nowd box SPLIT='!' ; col LABEL COLUMN TYPE LENGTH POSITION COUNT ; define LABEL / display width=40 "VARIABLE DESCRIPTION" spacing=0 left; define COLUMN / display width=13 "COLUMN" spacing=0 left format=$10.; define TYPE / display width=4 "TYPE" spacing=0 center format=$1.; define LENGTH / display width=8 "LENGTH!BYTES" spacing=0 right format=$6.; define POSITION / display width=8 "COLUMN POSITION" spacing=0 right format=7. ; define COUNT / display width=8 "DISTINCT!LEVELS" spacing=0 right format=6. ; quit; run; libname uin1 clear; libname uot1 clear; run; %MEND UTLCRDN; /*-------------------------------------*\ ! TESTCASE ! \*-------------------------------------*/ libname out "c:\windows\temp"; data out.testdata ( label="THIS IS TESTDATA" ) ; attrib people label="People of the world" length=$12 places label="Places of the world" length=$20 posesion label="posesion of the world" length=$30 income label="Income of the world" length=8 ; do people= 'Mary','Mike','Jeff','Jerry'; do places = 'MD','NY','TX','ME','CA'; do posesion = 'Car','Home','Clothes'; do income = 10000 to 90000 by 1000; if uniform( -1 ) lt .5 then output; end; end; end; end; run; libname out clear; run; %utlcrdn ( uin1=c:\windows\temp, uinmem11=testdata, uotmem11=datatest );