Good day all, I need to get the name of the 1st column formatted with DATE and NUM of a list of tables. I tried something like this in my slq proc and got results, but not the ones i liked. I think max/min isn't the way...prob have to use the attribute VARNUM of the DICTIONARY table but no idea what approach i can have, anyone can help me? Tks in advance.
proc sql;
create table want as
select
distinct UPCASE(t1.libname) as mylib,
UPCASE(t1.memname) as mytable,
max(case when (type='num' and xtype in ('','decimal')) then
upcase(t1.name) end ) as first_att_num,
max(case when (substr(format,1,4)='DATE' and xtype in ('date','num')) then
upcase(t1.name) end ) as first_att_date
from DICTIONARY.COLUMNS t1
right join have t2
on t2.libname = upcase(t1.libname)
and t2.memname = upcase(t1.memname)
group by t1.libname, t1.memname;
quit;
Sounds like you just need a HAVING clause to restrict to the FIRST, aka MIN(VARNUM), variable.
having t1.varnum = min(t1.varnum)
If you want to select the variables that have DATE format attached you can use the FMTINFO() function. This is much easier to do with the output of PROC CONTENTS than it is with DICTIONARY.COLUMNS because PROC CONTENTS puts just the format NAME into the FORMAT variable. The format Width and Decimal places are stored in separate variables.
'date'=fmtinfo(FORMAT,'cat')
But the format variable in DICTIONARY.COLUMNS has the complete format specification. So you will need to remove those extra characters before sending the value to the FMTINFO() function.
'date'=fmtinfo(prxchange('s/\d*\.\d*$//',-1,trim(FORMAT)),'cat')
So do something like this:
proc sql;
create table want as
select a.libname
, a.memname
, b.varnum
, b.name
, b.format
from have a
inner join dictionary.columns b
on a.libname=b.libname
and a.memname=b.memname
and 'date'=fmtinfo(prxchange('s/\d*\.\d*$//',-1,trim(b.FORMAT)),'cat')
group by 1,2
having b.varnum = max(b.varnum)
;
quit;
If we make the HAVE dataset be the list of all of the dataset in SASHELP then the result is:
Obs libname memname varnum name format
1 SASHELP AIR 1 DATE MONYY.
2 SASHELP BUY 1 DATE DATE9.
3 SASHELP CITIDAY 1 DATE DATE9.
4 SASHELP CITIMON 1 DATE MONYY7.
5 SASHELP CITIQTR 1 DATE YYQC6.
6 SASHELP CITIWK 1 DATE WEEKDATX16.
7 SASHELP CITIYR 1 DATE YEAR4.
8 SASHELP COUNTSERIES 1 Date DATE9.
9 SASHELP FAILURE 4 Day DOWNAME.
10 SASHELP GNGSMP2 9 Date DATE.
11 SASHELP GNP 1 DATE YYQ.
12 SASHELP MDV 7 SHIPDATE DATE7.
13 SASHELP NVST1 1 DATE DATE9.
14 SASHELP NVST2 1 DATE DATE9.
15 SASHELP NVST3 1 DATE DATE9.
16 SASHELP NVST4 1 DATE DATE9.
17 SASHELP NVST5 1 DATE DATE9.
18 SASHELP PRDSAL2 11 MONYR MONYY.
19 SASHELP PRDSAL3 11 DATE MONYY.
20 SASHELP PRDSALE 10 MONTH MONNAME3.
21 SASHELP PRICEDATA 1 date MONYY.
22 SASHELP RENT 1 DATE DATE9.
23 SASHELP RETAIL 2 DATE YYQ4.
24 SASHELP ROCKPIT 1 DATE DATE9.
25 SASHELP SNACKS 5 Date DATE9.
26 SASHELP STEEL 1 DATE YEAR.
27 SASHELP STOCKS 2 Date DATE.
28 SASHELP TOURISM 1 year YEAR4.
29 SASHELP USECON 1 DATE MONYY.
30 SASHELP WORKERS 1 DATE MONYY5.