Macros for SAS Application Developers
https://github.com/sasjs/core
mp_getcols.sas
Go to the documentation of this file.
1/**
2 @file
3 @brief Creates a dataset with column metadata.
4 @details This macro takes the `proc contents` output and "tidies it up" in the
5 following ways:
6
7 @li Blank labels are filled in with column names
8 @li Formats are reconstructed with default values
9 @li Types such as DATE / TIME / DATETIME are inferred from the formats
10
11 Example usage:
12
13 %mp_getcols(sashelp.airline,outds=work.myds)
14
15 @param ds The dataset from which to obtain column metadata
16 @param outds= (work.cols) The output dataset to create. Sample data:
17|NAME:$32.|LENGTH:best.|VARNUM:best.|LABEL:$256.|FMTNAME:$32.|FORMAT:$49.|TYPE:$1.|DDTYPE:$9.|
18|---|---|---|---|---|---|---|---|
19|`AIR `|`8 `|`2 `|`international airline travel (thousands) `|` `|`8. `|`N `|`NUMERIC `|
20|`DATE `|`8 `|`1 `|`DATE `|`MONYY `|`MONYY. `|`N `|`DATE `|
21|`REGION `|`3 `|`3 `|`REGION `|` `|`$3. `|`C `|`CHARACTER `|
22
23 <h4> Related Macros </h4>
24 @li mf_getvarlist.sas
25 @li mm_getcols.sas
26
27 @version 9.2
28 @author Allan Bowe
29
30**/
31
32%macro mp_getcols(ds, outds=work.cols);
33%local dropds;
34proc contents noprint data=&ds
35 out=_data_ (keep=name type length label varnum format:);
36run;
37%let dropds=&syslast;
38data &outds(keep=name type length varnum format label ddtype fmtname);
39 set &dropds(rename=(format=fmtname type=type2));
40 name=upcase(name);
41 if type2=2 then do;
42 length format $49.;
43 if fmtname='' then format=cats('$',length,'.');
44 else if formatl=0 then format=cats(fmtname,'.');
45 else format=cats(fmtname,formatl,'.');
46 type='C';
47 ddtype='CHARACTER';
48 end;
49 else do;
50 if fmtname='' then format=cats(length,'.');
51 else if formatl=0 then format=cats(fmtname,'.');
52 else if formatd=0 then format=cats(fmtname,formatl,'.');
53 else format=cats(fmtname,formatl,'.',formatd);
54 type='N';
55 if format=:'DATETIME' or format=:'E8601DT' then ddtype='DATETIME';
56 else if format=:'DATE' or format=:'DDMMYY' or format=:'MMDDYY'
57 or format=:'YYMMDD' or format=:'E8601DA' or format=:'B8601DA'
58 or format=:'MONYY'
59 then ddtype='DATE';
60 else if format=:'TIME' then ddtype='TIME';
61 else ddtype='NUMERIC';
62 end;
63 if label='' then label=name;
64run;
65proc sql;
66drop table &dropds;
67%mend mp_getcols;