I am creating a lot of formats from KEY and MAPPING tables through PROC FORMAT in the following way. However I have constantly problems with the NOTE in the subject. Is there a better way?
----------------
/************************************************************************************
* INFO *
* *
* Weil die Spalten Label und Start Label nicht gleichzeitig vom Type character und *
* numeric sein können, müssen verschiedene Format-Tabellen erzeugt werden ! *
* *
*************************************************************************************
* *
* Inhaltsverzeichnis *
* *
* A) Formate vorbereiten *
* 1) Formate mit Start numerisch und Label character erzeugen aus eigenen *
* Keys und künstlichen TBX-Tabellen *
* *
* 2) Formate mit Start numerisch und Label character erzeugen aus echten *
* TBX-Tabellen *
* *
* 3) Formate mit Start und Label numerisch erzeugen aus Mappings *
* *
* 4) Formate mit Start und Label character erzeugen aus echten TBX-Tabellen *
* *
* *
* B) Formate erzeugen *
* 1) Start numerisch und Label character *
* *
* 2) Start und Label numerisch *
* *
* 3) Start und Label numerisch *
* *
************************************************************************************/
/* to do Max: Erzeugung der Format-Tabelle automatisieren
proc sql;
describe table
dictionary.tables;
create table temp as
select *
from dictionary.tables
where libname="DB";
quit;
*/
/*
A) Formate vorbereiten
1) Formate mit Start numerisch und Label character erzeugen aus eigenen Keys und künstlichen TBX-Tabellen
*/
data FORMAT_SN_LC1;
set db.key_aktivpassiv (rename=(AktivPassiv=label AktivPassivID=start));
length FMTNAME $25;
FMTNAME = "AktivPassiv";
RUN;
data FORMAT_SN_LC2;
set db.key_CashflowTyp (rename=(CashflowTyp=label CashflowTypID=start));
FMTNAME = "CashflowTyp";
RUN;
data FORMAT_SN_LC3;
set db.key_PositionsTyp (rename=(PositionsTyp=label PositionsTypID=start));
FMTNAME = "PositionsTyp";
RUN;
data FORMAT_SN_LC4;
set db.key_MLR_produktgruppe (rename=(MLR_PRODUKTGRUPPE=label MLR_PRODUKTGRUPPEid=start));
FMTNAME = "MLR_PRODUKTGRUPPE";
RUN;
data FORMAT_SN_LC5;
set db.key_sollhaben (rename=(SollHaben=label SollHabenID=start));
FMTNAME = "SollHaben";
RUN;
data FORMAT_SN_LC6;
set db.key_szenario (rename=(Szenario=label SzenarioID=start));
FMTNAME = "Szenario";
RUN;
data FORMAT_SN_LC7;
set db.TBX_DA000F803 (rename=(VERTRAG_ZUSTAND=label VERTRAG_ZUSTAND_S=start));
FMTNAME = "Vertragszustand";
RUN;
data FORMAT_SN_LC8;
set db.KEY_JaNein (rename=(JaNein=label JaNeinID=start));
FMTNAME = "JaNein";
RUN;
data FORMAT_SN_LC9;
set db.TBX_UK000USTA (rename=(Umsatz_status_tx=label Umsatz_status_sl=start));
FMTNAME = "Umsatzstatus";
RUN;
/*
2) Formate mit Start numerisch und Label character aus TBX-Tabellen erzeugen
*/
data FORMAT_TBX_SN_LC1;
set AABREFTB.TBX_KT000BONI (rename=(BONITAETSTXT_LANG=LABEL BONITAETS_SL=START));
FMTNAME = "Bonitaetstext";
RUN;
data FORMAT_TBX_SN_LC2 (rename=(KTO_ZUSTAND_TX=Label));
set AABREFTB.TBX_KT000ZUST;
Start=Input(KTO_ZUSTAND_SL, 1.);
FMTNAME="Kontozustand";
run;
/*
3) Formate mit Start und Label numerisch erzeugen aus Mappings
*/
data FORMAT_M_SLN1;
set db.tmap_positionstyp2sollhaben (rename=(SOLLHABENID=LABEL POSITIONSTYPID=START));
length FMTNAME $25;
FMTNAME = "m_sollhaben";
RUN;
data FORMAT_M_SLN2;
set db.bmap_gab2mlr_produktgruppe (rename=(MLR_PRODUKTGRUPPEID=LABEL GAB=START));
FMTNAME = "m_gab2mlr_produktgruppe";
RUN;
/*
4) Formate mit Start und Label character erzeugen aus echten TBX-Tabellen
*/
data FORMAT_TBX_SLC1;
set AABREFTB.TBX_AB000PRDBZ (rename=(PRODUKTBEZEICHNUNG=LABEL));
START=CAT(PRODUKTNUMMER, PRODUKTUNTERNUMMER, WAEHRUNG);
FMTNAME="Produktname";
RUN;
proc sort data=format_tbx_slc1;
by start;
run;
data FORMAT_TBX_SLC2;
set AABREFTB.TBX_KT000DRTXT (rename=(DR_TEXT=LABEL));
ART = PUT(TEXT_KONTOART, Z2.);
SCHLÜSSEL = PUT(TEXTSCHL_1, z2.);
ZSCHLÜSSEL = PUT(TEXT_SL_ZUSATZ, Z4.);
START = CAT(ART, SCHLÜSSEL, ZSCHLÜSSEL);
FMTNAME="$Buchungstext";
RUN;
/*
B) Formate erzeugen
1) Start numerisch und Label character
*/
DATA FORMAT_SN_LC;
set format_SN_LC1 format_SN_LC2 format_SN_LC3 format_SN_LC4 format_SN_LC5 format_SN_LC6 FORMAT_SN_LC7 FORMAT_SN_LC8 FORMAT_SN_LC9
format_tbx_sn_lc1 format_tbx_sn_lc2;
RUN;
PROC FORMAT CNTLIN=FORMAT_SN_LC;
value date_val
0 - 100 = "invalid"
101 - 131 = "Ok"
132 - 200 = "invalid"
201 - 229 = "Ok"
230 - 300 = "invalid"
301 - 331 = "Ok"
332 - 400 = "invalid"
401 - 430 = "Ok"
431 - 500 = "invalid"
501 - 531 = "Ok"
532 - 600 = "invalid"
601 - 630 = "Ok"
631 - 700 = "invalid"
701 - 731 = "Ok"
732 - 800 = "invalid"
801 - 831 = "Ok"
832 - 900 = "invalid"
901 - 930 = "Ok"
931 - 1000 = "invalid"
1000 - 1031 = "Ok"
1032 - 1100 = "invalid"
1101 - 1130 = "Ok"
1131 - 1200 = "invalid"
1200 - 1231 = "Ok"
1232 - 9999 = "invalid"
. = "missing"
;
value missing
. = "missing"
OTHER = "OK"
;
RUN;
/*
2) Start und Label numerisch
*/
data formate_SLN;
set format_M_SLN1 format_M_SLN2;
run;
PROC FORMAT CNTLIN=FORMATE_SLN;
RUN;
/*
3) Start und Label character
*/
data formate_SLC;
length fmtname $13;
set format_tbx_SLC1 format_tbx_SLC2;
run;
PROC FORMAT CNTLIN=FORMATE_SLC LIB=INFO.FORMATS;
RUN;
Note updated as you don't need the select (your format matches the name.
Sorry, really can't read that code, try coding all in one case, use indents correctly etc. it makes reading the code so much easier. Also simplfy out your code, there is no need to do X amount of datasteps, just a logical thought on what goes in is enough:
data fmts (keep=label start); set db.key_aktivpassiv (rename=(aktivpassiv=label aktivpassivid=start)) db.key_cashflowtyp (rename=(cashflowtyp=label cashflowtypid=start)) ...
indsname=tmp;
fmtname=strip(tranwrd(scan(tmp,2,"."),"key_","")); run;
This code will effectively replace the series of datasteps you currently have. I would also question why you need so many formats, effectively duplicating the data which already exists?
Please limit the scope of your problem, and supply the LOG.
Note updated as you don't need the select (your format matches the name.
Sorry, really can't read that code, try coding all in one case, use indents correctly etc. it makes reading the code so much easier. Also simplfy out your code, there is no need to do X amount of datasteps, just a logical thought on what goes in is enough:
data fmts (keep=label start); set db.key_aktivpassiv (rename=(aktivpassiv=label aktivpassivid=start)) db.key_cashflowtyp (rename=(cashflowtyp=label cashflowtypid=start)) ...
indsname=tmp;
fmtname=strip(tranwrd(scan(tmp,2,"."),"key_","")); run;
This code will effectively replace the series of datasteps you currently have. I would also question why you need so many formats, effectively duplicating the data which already exists?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.